Run Macro follwing click on CommandButton Userform - Please Help!

[polasnkiman]

Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello Everyone,

I'm working on Excel 2004.

I've created a UserForm in VBA with one TextBox and 2 (two) CommandButtons (Proceed and Cancel). On my spreadsheet, I created a button thanks to the "Form" toolbar.
Now, not really being a VBA literate I would need some detailed help on how to implement the following:

1. Launch the UserForm when I click on the spreadsheet button that I created earlier (the user is then requested to input his data in the Userform's TextBox);

2. Then, when clicking the "Proceed" CommandButton on the Userform I would need the data inputted by the user in the UserForm TextBox to be copied in a specific cell (let's say in cell T11) immediately followed by the execution of a macro that I have already prepared. I would need for clarity purposes that the UserForm be closed as soon as the user cliks the "Proceed" CommandButton.

3. If the user clicks on the "Cancel" CommandButton, the UserForm would need to close and the macro not be run.

Aside from this procedure, since my macro is somehow pretty long, I would also like to incorporate a double progress bar in my macro so that the user is clearly aware that the macro is running.

Any detailed and accurate help would be highly appreciated as I have been turning around the pot on how to implement this with no success. I've really tried to figure it out but have always been limited by my lack of knowledge on how VBA really works and the inherent VBA coding. All I could find on Internet are chunks of codes that do not necessarily apply to my case, and every time I am missing something.

A step-by-step help would really be appreciated. If you need to email me for some reason, please do at: yanomami_grec[at]yahoo.fr

I would like to thank in advance all those that will spend some precious time helping me.

Paul

 

[JE McGimpsey]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

Hello Everyone, <br><br>I'm working on Excel 2004. <br><br>I've created a
UserForm in VBA with one TextBox and 2 (two) CommandButtons (Proceed and
Cancel). On my spreadsheet, I created a button thanks to the &quot;Form&quot;
toolbar. <br>

There are many ways to do this, but here's one way...

Assume:

1) A form button on the sheet associated with the macro ShowForm()
(Ctrl-click the button and choose Assign Macro... from the
contextual menu)

2) A Userform named MyForm that contains three controls:
a) Command button cbProceed
b) Command button cbCancel
c) Text box tbInput

In the Userform code module:

Option Explicit
Dim bCancel As Boolean 'set if user presses Cancel button

Property Get Cancel() As Boolean
'Retrieve whether user pressed cancel button or not
Cancel = bCancel
End Property

Property Get InputReturn() As Variant
'Retrieve the value entered in the text box
InputReturn = tbInput.Value
End Property

Private Sub UserForm_Initialize()
bCancel = False
End Sub

Private Sub cbCancel_Click()
bCancel = True
Me.Hide 'don't unload the form yet
End Sub

Private Sub cbProceed_Click()
Me.Hide 'don't unload the form yet
End Sub


This goes in a regular code module:

Option Explicit

Public Sub ShowForm()
Dim fmForm As MyForm 'Object variable for form
Set fmForm = New MyForm 'create new instance of the form
With fmForm
.Show
If Not .Cancel Then
Range("T11").Value = .InputReturn
MyMacro
End If
End With
Unload fmForm 'don't unload the form until done with it.
Set fmForm = Nothing 'destroy the object
End Sub

Public Sub MyMacro()
'Do something here
MsgBox "My Macro Ran"
End Sub

 

[polasnkiman]

Hello,

All I can is SWEET! It works just as I wanted. The end part does not work thought. The message who's supposed to pop up saying that the Macro has run simply doesn't appear. Correct me if I am wrong, but under: &quot;Range(&quot;T11&quot.Value = .InputReturn&quot; I have to copy/paste the macro I made, and at &quot;Public Sub MyMacro()&quot; I have to replace &quot;MyMacro&quot; by the name of the macro I copy/pasted just before ?

Is there any possibility to include a progress bar instead ? Not sure why the Macro takes up to 20 seconds to run on a Mac while on a PC is nearly instantaneous...

In any case I have to thank you for having taken the time to help me.

All the best.

Paul

 

[polasnkiman]

By the way I've tried integrating the following code within the one you sent me earlier :

Private Sub cbProceed_Click()
&nbsp;&nbsp;&nbsp;&nbsp;Me.Hide 'don't unload the form yet
&nbsp;&nbsp;&nbsp;&nbsp;If Me.tbInput.value = &quot;&quot; Then
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;Error: Please enter a Value.&quot;, vbExclamation, &quot;Expected value&quot;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me.tbInput.SetFocus
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub
&nbsp;&nbsp;&nbsp;&nbsp;End If
If Not IsNumeric(Me.tbInput.value) Then
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;Error: Only numbers allowed.&quot;, vbExclamation, &quot;Expected Numerical Value&quot;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me.tbInput.SetFocus
Exit Sub
End If
End Sub

Although a window successfully pops up if I leave the textbox blank or I enter a letter, the macro starts running as soon as I click ok to confirm the error...which is obviously not what I am looking for. What am I doing wrong ?

 

[polasnkiman]

no one ?

 

[JE McGimpsey]

Hello, <br><br>All I can is SWEET! It works just as I wanted. The end part
does not work thought. The message who's supposed to pop up saying that the
Macro has run simply doesn't appear. Correct me if I am wrong, but under:
&quot;Range(&quot;T11&quot.Value = .InputReturn&quot; I have to copy/paste
the macro I made, and at &quot;Public Sub MyMacro()&quot; I have to replace
&quot;MyMacro&quot; by the name of the macro I copy/pasted just before ?
<br><br>Is there any possibility to include a progress bar instead ? Not sure
why the Macro takes up to 20 seconds to run on a Mac while on a PC is nearly
instantaneous... <br><br>In any case I have to thank you for having taken the
time to help me. <br><br>All the best. <br><br>Paul

I can barely read your post since it's in HTML, but no, you don't paste
your macro into that line of code, it should stand alone.

If I've misinterpreted - Is your macro in a regular code module?

 

[JE McGimpsey]

Not sure why my post is unreadable...

not your fault at all - but this last one is simply indecipherable for
me. I read the newsgroups with a newsreader, but the web interface turns
spaces into &nbsp;s, ampersands to &amp;s, and quotation marks into
&quot;s.

Perhaps someone who reads the group with a web browser will be able to
help.

 

[polasnkiman]

Ok. Thanks.