Run Macro follwing click on CommandButton Userform - Please Help!

[polas...@officeformac.com]

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]

In article <59bb1...@webcrossing.JaKIaxP2ac0>,
polas...@officeformac.com wrote:

> 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

[polas...@officeformac.com]

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: "Range("T11").Value = .InputReturn" I have to copy/paste the macro I made, and at "Public Sub MyMacro()" I have to replace "MyMacro" 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

[polas...@officeformac.com]

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

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

    If Me.tbInput.value = "" Then
        MsgBox "Error: Please enter a Value.", vbExclamation, "Expected value"
        Me.tbInput.SetFocus
        Exit Sub
    End If
If Not IsNumeric(Me.tbInput.value) Then
        MsgBox "Error: Only numbers allowed.", vbExclamation, "Expected Numerical Value"
        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 ?

[polas...@officeformac.com]

no one ?

[JE McGimpsey]

In article <59bb1...@webcrossing.JaKIaxP2ac0>,
polas...@officeformac.com wrote:

> 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?

[polas...@officeformac.com]

Not sure why my post is unreadable... I can clearly see it on the website.. Anyways, yes my macro is in a regular module. This is what I ended up doing with the code you sent me and it seems to work fine: (for your understanding "I3_Sensitivity_Analysis_BOI_and_no_BOI" is the name of my macro)

Option Explicit

Public Sub ShowFormSensiAna()
    Dim fmForm As FrmSensiAna 'Object variable for form
    Set fmForm = New FrmSensiAna 'create new instance of the form

    With fmForm
        .Show
        If Not .Cancel Then

            Range("S15").value = .InputReturn
        Application.ScreenUpdating = False
            Call I3_Sensitivity_Analysis_BOI_and_no_BOI
        Application.ScreenUpdating = True
        MsgBox "Simulation Succesfully Achieved" & vbCr & "" & vbCr & "Please proceed to 'SENSITIVITY ANALYSIS' tab for results"

        End If
    End With
    Unload fmForm 'don't unload the form until done with it.
    Set fmForm = Nothing 'destroy the object
End Sub

However in the userform's code I've tried to incorporate the additional code (here-below). What I am attempting to do is to avoid that a user leaves the textbox blank or enters a letter instead of a numerical value. However, when I try running the form and if I click the proceed button without entering any value or entering a non-numerical value an error message pops up (which is what I want) nonetheless the macro starts running as soon as I click the "Ok" button to confirm the error...which is obviously not what I am looking for. Normally it should return to the userform for the user to input a numerical value. What is wrong ?

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

Thanks again and hope it's clear this time

[JE McGimpsey]

In article <59bb1...@webcrossing.JaKIaxP2ac0>,
polas...@officeformac.com wrote:

> 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.

[polas...@officeformac.com]

Ok. Thanks.