Excel VBA Dialog Box 1

[WBURKERT]

I am trying to create a dialog box that has just two command buttons - ENGLISH and METRIC. I got the box and the buttons working but clicking on either button fails to exit the sub and then I am not sure how to pass the clicked button's value back to my macro that is nothing more than a bunch of IF THEN statements depending on whether ENGLISH or METRIC was pushed.

Your help is much appreciated.

Sub Data_Change()
Dim UNITS As String
Load frmUNITS
frmUNITS.Show

If UNITS = English Then
Worksheets("RUN 1").Range("E7").Value = "inHg"
Else
Worksheets("RUN 1").Range("E7").Value = "mmHg"
End If




frmUNITS

Private Sub commandButton1_Click()
DIM UNITS As String
If CommandButton1_Click().Value = TRUE Then
UNITS = English
End If
frmUNITS.Hide
End Sub

Private Sub CommandButton2_Click()
If CommandButton2_Click() = TRUE Then
UNITS = METRIC
End If
frmUNITS.Hide
End Sub

 

[SkipVought]

When I enter Data_Change(UNITS As String) the macro disappears from Excel 2010.

There is nowhere, where is have coded...

Data_Change(UNITS As String)

I did code...

Sub Data_Change(UNITS As String)

With the code as you have it, using

Sub Data_Change(UNITS As String)

please post ALL you code, indicating also what UserForm or module each is in.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WBURKERT]

Skip,

Thanks for your assistance.

I am trying to pass the name of the command button back to the main sub so I can make decisions

 

[SkipVought]

This code has been tested
frmUNITS:

Private Sub CommandButton1_Click()
    Data_Change "English"
End Sub

Private Sub CommandButton2_Click()
    Data_Change "Metric"
End Sub

Module1:

Sub Data_Change(UNITS As String)

    frmUNITS.Hide

    Select Case UNITS
      Case "English"
        Worksheets("RUN 1").Range("E7").Value = "in Hg"
        Worksheets("RUN 1").Range("E8").Value = "in H20"
        Worksheets("RUN 1").Range("E9").Value = "in Hg"
        Worksheets("RUN 1").Range("E13").Value = "lb/lb-mole"
        Worksheets("RUN 1").Range("E14").Value = "lb/lb-mole"
      Case "Metric"
        Worksheets("RUN 1").Range("E7").Value = "mm Hg"
        Worksheets("RUN 1").Range("E8").Value = "mm H20"
        Worksheets("RUN 1").Range("E9").Value = "mm Hg"
        Worksheets("RUN 1").Range("E13").Value = "g/g-mole"
        Worksheets("RUN 1").Range("E14").Value = "g/g-mole"
    End Select

End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WBURKERT]

Skip,

I truly appreciate your commitment to total satisfaction.

While I am not an expert by any means I think I can get basics done and certainly copy your code. Have you tried this Excel 2010? I gotta tell you, once again, that when I put (UNITS As String) after Sub Data_Change and save everything the macro does not appear when I view macros, all of the Macro options are ghosted out because there are no macros. If I record a simple macro and go back and edit the newly recorded macro the Change_Data macro is listed under the modules but I can't RUN it because the only macro listed is the simple macro.

Either something is bugged up in 2010, or I don't know something about (UNITS As String), or I don't know something about modules and macros.

If I put DIM UNITS As String after Sub Data_Change then things appear normal except I get an error during compile.

I think you should really try this with Excel 2010 and I think you'll see what I am talking about. I tried this at work and at home, two completely unique PC's, both running EXCEL 2010.

Patience is a virtue.

Bill

 

[WBURKERT]

This there not a Load frmUNITS and frmUNITS.Show somewhere?

 

[SkipVought]

Data_Change will NOT be in Macros, because it cannot be run from Macros, since there is a requred argument.

FORGET THAT!!!

My code should run EXACTLY as posted.

Did you try that, without modifying ANY code? Please load the form and TRY!!!

When either button is clicked, after the frmUNITS is loaded, the appropriate values will be written to the RUN 1 sheet.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

This there not a Load frmUNITS and frmUNITS.Show somewhere?

Loading or showing the form has NOTHING to do with the requirement of clicking a button and processing that event.

This entire thread assumes that the form has been loaded and is wiating for some event.

Again Load or SHow is not related to ANYTHING we are discussing!



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WBURKERT]

Skip,

I have your code entered exactly as you have shown, now what?

How does the macro run? How does the Userform pop up and display ENGLISH or METRIC?

I am following your example and don't know what's supposed to happen next.

 

[SkipVought]

This thread has been discussing the two button click events. You need to have a process that shows your form. THAT process is started by an event, like hitting the RUN button in Macros. It might be as simple as...

Sub LoadUNITS()
   frmUNITS.Show
end sub

You can also show the form by hitting F5 when your form is displayed in the VB Editor.

Once the form is loaded, it sits there waiting for the user to initiate some event; for instance, clicking a button. The button may NEVER be clicked before the form is unloaded. THAT is why LOADING or SHOWING the form has NOTHING to do with the events that occur related to the controls on the form.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WBURKERT]

everything is working and I have learned so much again.

Thank-you Skip

 

[SkipVought]

Sorry that it took me so long to perceive your basic issues.

Glad you got things working, and pleased to be of help.

Thanks.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]