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]

Somethig like this...

Sub Data_Change(UNITS As String)

    Select Case UNITS
        Case "English"
        Worksheets("RUN 1").Range("E7").Value = "inHg"
        Case "Metric"
        Worksheets("RUN 1").Range("E7").Value = "mmHg"
    End Select
End Sub

Private Sub commandButton1_Click()
    Data_Change "English"
End Sub

Private Sub CommandButton2_Click()
    Data_Change "Metric"
End Sub

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

 

[nnett]

The following should work if, UNITS was only used to set the cell values then the public declare can be removed as well as the setting of the value in the command button code


Public UNITS As String

Sub Data_Change()

Load frmUNITS
frmUNITS.Show
End Sub


frmUNITS

Private Sub commandButton1_Click()
Worksheets("RUN 1").Range("E7").Value = "inHg"
UNITS = "English"
frmUNITS.Hide
End Sub

Private Sub CommandButton2_Click()
Worksheets("RUN 1").Range("E7").Value = "mmHg"
UNITS = METRIC
frmUNITS.Hide
End Sub

 

[nnett]

note should be in the above

UNITS = "METRIC

 

[WBURKERT]

This is what I have but a compile error coming from Data_Change that I am now trying to fix. The compile error is:

"Wrong Number of arguments or invalid property assinment"

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

'
'STACK DATA FIELDS
'
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

Private Sub CommandButton1_Click()
Data_Change "English"
End Sub

Private Sub CommandButton2_Click()
Data_Change "Metric"
End Sub

 

[SkipVought]

Sub Data_Change([b]UNITS As String[/b])[s]
Dim UNITS As String[/s]
Load UserForm1
UserForm1.Show
...

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

 

[WBURKERT]

Skip,

I tried this from yesterday's reply but it seems to mess up EXCEL and MACROs. I am using EXCEL 2010 and when I make your change then go to RUN the MACRO no macro's exist.

I tried it again this morning with the same kinda results.

I also can't STEP INTO the MACRO after I make that change either. If I take out UNITS As String and just leave the parens then I can RUN and/or STEP INTO.

Is this a 2010 bug?

 

[SkipVought]

Where is Sub Data_Change stored?

Where are the button click codes stored?

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

 

[WBURKERT]

)

You just ask something that I am totally not sure about.

I think they are stored within the speific workbook project but you may have to tell me how to know and where to look.

I just typed in the macro a second time from scratch and get the same symptoms.

 

[SkipVought]

look in the Project Explorer, ctr+R


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

 

[WBURKERT]

The Module and Forms and listed under the VBAProject (ISOKINETIC.xlsm)

 

[SkipVought]

In that project you have userforms, modules, worksheets and workbook.

In what module or userform do each of the procedures in question reside?

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

 

[WBURKERT]

VBAProject (ISOKINETIC.xlsm)
Microsoft Excel Objects
Sheet1 (NOTES)
Sheet2 (RUN 1)
ThisWorkbook
Forms
frmUNITS
Modules
Module1

 

[SkipVought]

"In what module or userform do each of the procedures in question reside?"

Must I spell it out?

In which VBA object does each of these reside???
[tt]
Sub Data_Change()

Private Sub CommandButton1_Click()

Private Sub CommandButton2_Click()

[/tt]

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

 

[WBURKERT]

Sub Data_Change() resides in Modules and is named Module1

Private Sub CommandButton1_Click() resides in Forms and is called frmUNITS

Private Sub CommandButton2_Click() also resides in Forms and is called frmUNITS

If this is still not what you are after then maybe you might have to spell it out :0(

 

[WBURKERT]

I have this working quite nicely but I can't detect when the METRIC command button is clicked, any ideas. (I really don't want to change too much logic because things are working. Elegence, efficiency and execution time are not a concern so please help me get past this current hurdle of detecting the METRIC command button. Thanks in advance.

Sub Data_Change()
Dim UNITS As String
Load frmUNITS
frmUNITS.Show
'
'STACK DATA FIELDS
'
If UNITS = English Then
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"
Else
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 If

FORMS
frmUNITS

Private Sub cmdENGLISH_Click()
UNITS = English
frmUNITS.Hide
End Sub
Private Sub cmdMETRIC_Click()
UNITS = Metric
frmUNITS.Hide
End Sub

 

[SkipVought]

I give up!

You have totally disregarded my code suggestions.

i cannot do anything else to help you.

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

 

[WBURKERT]

its more like I can't the pieces of your suggestions to work. It seems as though you feed cryptic examples never telling anyone where to insert the suggestions. I do not mind being made to try but time does not allow for reinventing the wheel.

You helped alot with the command button click event but all I have left is to pass the value of UNITS back to my main sub.

You'll see if the cmdENGLISH button is clicked I would like UNITS to equal ENGLISH and when the cmdMETRIC button is clicked I would like UNITS to equal METRIC.

Then in my main macro I would like to determine the value of UNITS and make the right IF THEN decisions based on that value.

I could really use some help on how to do that because it seems as though it is just a variable. Whoever can help, please do. It is much appreciated.

Bill

 

[SkipVought]

I gave you a straight answer on 19 Aug 10 16:19.

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

 

[WBURKERT]

Maybe, but that gave me a compile error on Data_Change. I just changed, again, everything that works to your suggestions and get the same compile error.

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

While you may have provided support it did not work in Excel 2010. Then I tell you three times where everything is and I feel as though I am being berated "because your ambigous questions are hard for a novice to answer."

This is not the first time you ahve tried to change all the logic that I designed to work the way you want it to but only providing partial support.

I have one peice of my puzzle left and sure hope someone can be kind enough to help me pass a varible between the two subs - THAT'S ALL I AM ASKING. Thanks in advance.