Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

custom dialogue box variable entry trouble

  Asked By: Kiral    Date: Mar 09    Category: MS Office    Views: 1316
  

I'm having trouble using a custom dialogue box to input variables into
an equation. when i hit "enter" the box does not close and the
variables are not entered into the program. my code is below; can
anyone help?

Option Explicit

Private Sub Enter_Click()

Dim A As Variant
Dim HF As Variant
Dim HI As Variant
Dim BW As Variant
Dim TCM As Variant
Dim TCS As Variant
Dim Treadmill As Boolean
Dim Stairmill As Boolean
Dim Maximal As Boolean
Dim Submaximal As Boolean

A = InputAge.Value
HF = InputHeightFeet.Value
HI = InputHeightInches.Value
BW = InputWeight.Value
TCM = InputTestTimeMin.Value
TCS = InputTestTimeSeconds.Value
Treadmill = Tread.Value
Stairmill = Stair.Value
Maximal = Max.Value
Submaximal = Submax.Value

Unload VO2Input

End Sub

Private Sub Quit_Click()

Unload VO2Input
End

End Sub





Option Explicit

Sub MaxVO2Predict()

'dimensionalizing variables
Dim A As Variant
Dim HF As Variant
Dim HI As Variant
Dim BW As Variant
Dim TCM As Variant
Dim TCS As Variant
Dim PeakVO2 As Variant
Dim MTInt As Single
Dim MTTC As Single
Dim MTBMI As Single
Dim MSInt As Single
Dim MSTC As Single
Dim MSBMI As Single
Dim ST208Int As Single
Dim ST208TC As Single
Dim ST208BMI As Single
Dim SS208Int As Single
Dim SS208TC As Single
Dim SS208BMI As Single
Dim Treadmill As Boolean
Dim Stairmill As Boolean
Dim Maximal As Boolean
Dim Submaximal As Boolean
Dim VO2Input As Object
Dim n As Long

'input prediction equation coefficients from appropriate cells
MTInt = Sheets("Equations").Cells(8, 14)
MTTC = Sheets("Equations").Cells(9, 14)
MTBMI = Sheets("Equations").Cells(10, 14)
MSInt = Sheets("Equations").Cells(8, 17)
MSTC = Sheets("Equations").Cells(9, 17)
MSBMI = Sheets("Equations").Cells(10, 17)
ST208Int = Sheets("Equations").Cells(15, 4)
ST208TC = Sheets("Equations").Cells(16, 4)
ST208BMI = Sheets("Equations").Cells(17, 4)
SS208Int = Sheets("Equations").Cells(15, 7)
SS208TC = Sheets("Equations").Cells(16, 7)
SS208BMI = Sheets("Equations").Cells(17, 7)

Set VO2Input = New VO2Input
VO2Input.Show

Sheets("Prediction").Cells(22, 2) = A

If Treadmill = True And Maximal = True Then
PeakVO2 = MSInt + ((TCM + (TCS / 60)) * MSTC) + (((BW * 0.453592)
/ (((HF / 12) + HI) * 0.0254) ^ 2) * MSBMI)
ElseIf Stairmill = True And Maximal = True Then
PeakVO2 = MTInt + ((TCM + (TCS / 60)) * MTTC) + (((BW * 0.453592)
/ (((HF / 12) + HI) * 0.0254) ^ 2) * MTBMI)
ElseIf Treadmill = True And Submaximal = True Then
PeakVO2 = ST208Int + ((TCM + (TCS / 60)) * ST208TC) + (((BW *
0.453592) / (((HF / 12) + HI) * 0.0254) ^ 2) * ST208BMI)
ElseIf Stairmill = True And Submaximal = True Then
PeakVO2 = SS208Int + ((TCM + (TCS / 60)) * SS208TC) + (((BW *
0.453592) / (((HF / 12) + HI) * 0.0254) ^ 2) * SS208BMI)
End If

'score is diplayed in appropriate cell
Cells(14, 3) = PeakVO2

'output the answer into the message box
MsgBox ("The predicted maximal oxygen uptake of this subject equals "
& Format(PeakVO2, "##.#") & " mL/kg/min")

n = Sheets("Equations").Cells(19, 14)

'output data from subject into database
Sheets("Data").Cells(2 + n, 1) = (n + 1)
Sheets("Data").Cells(2 + n, 2) = A
Sheets("Data").Cells(2 + n, 3) = (HF / 12) + HI
Sheets("Data").Cells(2 + n, 4) = ((HF / 12) + HI) * 2.54
Sheets("Data").Cells(2 + n, 5) = BW
Sheets("Data").Cells(2 + n, 6) = BW / 2.205
Sheets("Data").Cells(2 + n, 7) = (BW / 2.205) / ((((HF / 12 + HI) *
2.54) / 100) ^ 2)
Sheets("Data").Cells(2 + n, 8) = TCM + (TCS / 60)
Sheets("Data").Cells(2 + n, 9) = PeakVO2
If Stairmill = True Then
Sheets("Data").Cells(2 + n, 10) = "Stairmill"
ElseIf Treadmill = True Then
Sheets("Data").Cells(2 + n, 10) = "Treadmill"
End If
If Maximal = True Then
Sheets("Data").Cells(2 + n, 11) = "Maximal"
ElseIf Submaximal = True Then
Sheets("Data").Cells(2 + n, 11) = "Submaximal"
End If

End Sub

Share: 

 

9 Answers Found

 
Answer #1    Answered By: Silvia Chapman     Answered On: Mar 09

Without going too deep, it looks like you've dimensioned the variables
as "local" variables.

Keep in mind that any variables  declared WITHIN a subroutine
are available only to that subroutine.
so, in your Click event, when you have

Dim A as Variant
"A" only has a value until the End Sub statement and is not available
to any other subroutine.

You should move the variable  declarations to immediately following the
Option Explicit
in one of the Modules (preferably the first Module)
Then, be sure to REMOVE any of the Dim statements from the other subs.

Just to test this,
try this:
Option Explicit
Dim T
Sub Test1()
Dim I
For I = 1 To 5
T = I
Debug.Print "Inside Test1, T = " & T & ", I = " & I
Test2
Next I
End Sub
Sub Test2()
Dim I
Debug.Print "Inside Test2, T = " & T & ", I = (" & I & ")"
For I = 1 To 5
Debug.Print "Test Loop: " & T & ": I = " & T & " Test2, I = " & I
Next I
End Sub


then check your Immediate window.
You'll see:

nside Test1, T = 1, I = 1
Inside Test2, T = 1, I = ()
Test Loop: 1: I = 1 Test2, I = 1
Test Loop: 1: I = 1 Test2, I = 2
Test Loop: 1: I = 1 Test2, I = 3
Test Loop: 1: I = 1 Test2, I = 4
Test Loop: 1: I = 1 Test2, I = 5
Inside Test1, T = 2, I = 2
Inside Test2, T = 2, I = ()
Test Loop: 2: I = 2 Test2, I = 1
Test Loop: 2: I = 2 Test2, I = 2
Test Loop: 2: I = 2 Test2, I = 3
Test Loop: 2: I = 2 Test2, I = 4
Test Loop: 2: I = 2 Test2, I = 5
Inside Test1, T = 3, I = 3
Inside Test2, T = 3, I = ()
Test Loop: 3: I = 3 Test2, I = 1
Test Loop: 3: I = 3 Test2, I = 2

Because variables declared "outside" of a sub are available to all subs.
(you may have to declare them Global)
while variables declared "inside" a sub are only available to that sub.

 
Answer #2    Answered By: Ty Thompson     Answered On: Mar 09

when i remove the declarations from my click button sub it will not
work, saying the variables  are undefined. when i place the
declarations before each sub, i still receive no answer and my
variables are considered "empty"; this is what i have tried following
your advice.


Option Explicit

'dimensionalizing variables
Dim A As Variant
Dim HF As Variant
Dim HI As Variant
Dim BW As Variant
Dim TCM As Variant
Dim TCS As Variant
Dim PeakVO2 As Variant
Dim Treadmill As Boolean
Dim Stairmill As Boolean
Dim Maximal As Boolean
Dim Submaximal As Boolean

Sub MaxVO2Predict()

'dimensionalizing variables
Dim MTInt As Single
Dim MTTC As Single
Dim MTBMI As Single
Dim MSInt As Single
Dim MSTC As Single
Dim MSBMI As Single
Dim ST208Int As Single
Dim ST208TC As Single
Dim ST208BMI As Single
Dim SS208Int As Single
Dim SS208TC As Single
Dim SS208BMI As Single
Dim VO2Input As Object
Dim n As Long

'input prediction equation  coefficients from appropriate cells
MTInt = Sheets("Equations").Cells(8, 14)
MTTC = Sheets("Equations").Cells(9, 14)
MTBMI = Sheets("Equations").Cells(10, 14)
MSInt = Sheets("Equations").Cells(8, 17)
MSTC = Sheets("Equations").Cells(9, 17)
MSBMI = Sheets("Equations").Cells(10, 17)
ST208Int = Sheets("Equations").Cells(15, 4)
ST208TC = Sheets("Equations").Cells(16, 4)
ST208BMI = Sheets("Equations").Cells(17, 4)
SS208Int = Sheets("Equations").Cells(15, 7)
SS208TC = Sheets("Equations").Cells(16, 7)
SS208BMI = Sheets("Equations").Cells(17, 7)

Set VO2Input = New VO2Input
VO2Input.Show

Sheets("Prediction").Cells(22, 2) = A

If Treadmill = True And Maximal = True Then
PeakVO2 = MSInt + ((TCM + (TCS / 60)) * MSTC) + (((BW * 0.453592)
/ (((HF / 12) + HI) * 0.0254) ^ 2) * MSBMI)
ElseIf Stairmill = True And Maximal = True Then
PeakVO2 = MTInt + ((TCM + (TCS / 60)) * MTTC) + (((BW * 0.453592)
/ (((HF / 12) + HI) * 0.0254) ^ 2) * MTBMI)
ElseIf Treadmill = True And Submaximal = True Then
PeakVO2 = ST208Int + ((TCM + (TCS / 60)) * ST208TC) + (((BW *
0.453592) / (((HF / 12) + HI) * 0.0254) ^ 2) * ST208BMI)
ElseIf Stairmill = True And Submaximal = True Then
PeakVO2 = SS208Int + ((TCM + (TCS / 60)) * SS208TC) + (((BW *
0.453592) / (((HF / 12) + HI) * 0.0254) ^ 2) * SS208BMI)
End If

'score is diplayed in appropriate cell
Cells(14, 3) = PeakVO2

'output the answer into the message box
MsgBox ("The predicted maximal oxygen uptake of this subject equals "
& Format(PeakVO2, "##.#") & " mL/kg/min")

n = Sheets("Equations").Cells(19, 14)

'output data from subject into database
Sheets("Data").Cells(2 + n, 1) = (n + 1)
Sheets("Data").Cells(2 + n, 2) = A
Sheets("Data").Cells(2 + n, 3) = (HF / 12) + HI
Sheets("Data").Cells(2 + n, 4) = ((HF / 12) + HI) * 2.54
Sheets("Data").Cells(2 + n, 5) = BW
Sheets("Data").Cells(2 + n, 6) = BW / 2.205
Sheets("Data").Cells(2 + n, 7) = (BW / 2.205) / ((((HF / 12 + HI) *
2.54) / 100) ^ 2)
Sheets("Data").Cells(2 + n, 8) = TCM + (TCS / 60)
Sheets("Data").Cells(2 + n, 9) = PeakVO2
If Stairmill = True Then
Sheets("Data").Cells(2 + n, 10) = "Stairmill"
ElseIf Treadmill = True Then
Sheets("Data").Cells(2 + n, 10) = "Treadmill"
End If
If Maximal = True Then
Sheets("Data").Cells(2 + n, 11) = "Maximal"
ElseIf Submaximal = True Then
Sheets("Data").Cells(2 + n, 11) = "Submaximal"
End If

End Sub

Option Explicit

'dimensionalizing variables
Dim A As Variant
Dim HF As Variant
Dim HI As Variant
Dim BW As Variant
Dim TCM As Variant
Dim TCS As Variant
Dim PeakVO2 As Variant
Dim Treadmill As Boolean
Dim Stairmill As Boolean
Dim Maximal As Boolean
Dim Submaximal As Boolean

Private Sub Enter_Click()

A = InputAge.Value
HF = InputHeightFeet.Value
HI = InputHeightInches.Value
BW = InputWeight.Value
TCM = InputTestTimeMin.Value
TCS = InputTestTimeSeconds.Value
Treadmill = Tread.Value
Stairmill = Stair.Value
Maximal = Max.Value
Submaximal = Submax.Value

Unload VO2Input

End Sub

Private Sub Quit_Click()

Unload VO2Input
End

End Sub

 
Answer #3    Answered By: Grady Stewart     Answered On: Mar 09

As I mentioned, you may have to dimension the variables  as Global, or Public as:

Global A as Variant
Global HF as Variant

or

Public A as Variant
Public HF as Variant

I believe either one will work.

I think the problem is that regular DIMensioned variables are only available in
the Modules, or UserForms
where they are declared.
If I remember right, Global variables are available to all
modules/classes/UserForms in the Application.
Public variables are available to other MS Office Applications.

(I may have those reversed)

Nevertheless, that should solve the problem.

 
Answer #4    Answered By: Brendan Smith     Answered On: Mar 09

It's not clear what you are trying to do. You seem to be putting the
results of calculations into cells in the spreadsheet. This is what you
would typically want to do.

However, you are also declaring a set of variables  (A, HI, etc) in two
modules. This means that you will have two of each variable  - and they will
be separate variables.

If you want to have global variables (and you shouldn't really), then they
must be declared only once. The declaration should have Public, not Dim,
and the references to them from the other module should be preceded by the
module name and a dot.

E.g. in module AA you have Public XX as Integer. In module AA, you can then
refer to XX as XX. In module BB you refer to it as AA.XX so that Excel
knows where to find it.

This "dot" notation is always the best thing to do. However, in some cases,
Excel doesn't require it and will just find the public variable anyway -
provided there's only one with that name. But please use dot notation -
it's easier to debug.

 
Answer #5    Answered By: Faiza Mian     Answered On: Mar 09

that helped a lot, thanks. one more problem... when i hit  enter on
the dialogue box, the box  doesn't disappear, and the macro won't work
unless i hit the "x" in the top right of the box to clear it. hitting
cancel obviously cancels the macro. how do i get it to clear after i
hit enter  and run the calculation?

Option Explicit

Private Sub Enter_Click()

MaxVO2Predict.A = InputAge.Value
MaxVO2Predict.HF = InputHeightFeet.Value
MaxVO2Predict.HI = InputHeightInches.Value
MaxVO2Predict.BW = InputWeight.Value
MaxVO2Predict.TCM = InputTestTimeMin.Value
MaxVO2Predict.TCS = InputTestTimeSeconds.Value
MaxVO2Predict.Treadmill = Tread.Value
MaxVO2Predict.Stairmill = Stair.Value
MaxVO2Predict.Maximal = Max.Value
MaxVO2Predict.Submaximal = Submax.Value

Unload VO2Input

End Sub

Private Sub Quit_Click()

Unload VO2Input
End

End Sub

 
Answer #6    Answered By: Felix Gray     Answered On: Mar 09

Not sure what you mean. Are you saying that Enter_Click doesn't run until
you X the box? If so, no idea, but I can have a look when I get back from
work in 8 hours or so.

I assume what you want to clear is VO2Input. If so, I'd have expected the
Unload to do that.

 
Answer #7    Answered By: Sultana Tabassum     Answered On: Mar 09

I would first "hide" the dialog box  (so it disappears)
then, after the calculation is complete (or after you've stored all of the
variables
from the dialog box, then "unload" it from within the macro.

 
Answer #8    Answered By: Hollie Hughes     Answered On: Mar 09

thanks for the help so far. i'm trying to hide the dialogue box, but
i get a runtime error 402 - must close  or hide topmost modal form
first. i don't know how fix this.


Option Explicit

'dimensionalizing variables
Public A As Variant
Public HF As Variant
Public HI As Variant
Public BW As Variant
Public TCM As Variant
Public TCS As Variant
Public PeakVO2 As Variant
Public Treadmill As Boolean
Public Stairmill As Boolean
Public Maximal As Boolean
Public Submaximal As Boolean

Sub MaxVO2Predict()

'dimensionalizing variables
Dim MTInt As Single
Dim MTTC As Single
Dim MTBMI As Single
Dim MSInt As Single
Dim MSTC As Single
Dim MSBMI As Single
Dim ST208Int As Single
Dim ST208TC As Single
Dim ST208BMI As Single
Dim SS208Int As Single
Dim SS208TC As Single
Dim SS208BMI As Single
Dim VO2Input As Object
Dim n As Long

'input prediction equation  coefficients from appropriate cells
MTInt = Sheets("Equations").Cells(8, 14)
MTTC = Sheets("Equations").Cells(9, 14)
MTBMI = Sheets("Equations").Cells(10, 14)
MSInt = Sheets("Equations").Cells(8, 17)
MSTC = Sheets("Equations").Cells(9, 17)
MSBMI = Sheets("Equations").Cells(10, 17)
ST208Int = Sheets("Equations").Cells(15, 4)
ST208TC = Sheets("Equations").Cells(16, 4)
ST208BMI = Sheets("Equations").Cells(17, 4)
SS208Int = Sheets("Equations").Cells(15, 7)
SS208TC = Sheets("Equations").Cells(16, 7)
SS208BMI = Sheets("Equations").Cells(17, 7)

Set VO2Input = New VO2Input
VO2Input.Show

If Treadmill = True And Maximal = True Then
PeakVO2 = MSInt + ((TCM + (TCS / 60)) * MSTC) + ((BW * 0.453592) /
((((HF * 12) + HI) * 0.0254) ^ 2) * MSBMI)
ElseIf Stairmill = True And Maximal = True Then
PeakVO2 = MTInt + ((TCM + (TCS / 60)) * MTTC) + ((BW * 0.453592) /
((((HF * 12) + HI) * 0.0254) ^ 2) * MTBMI)
ElseIf Treadmill = True And Submaximal = True Then
PeakVO2 = ST208Int + ((TCM + (TCS / 60)) * ST208TC) + ((BW *
0.453592) / ((((HF * 12) + HI) * 0.0254) ^ 2) * ST208BMI)
ElseIf Stairmill = True And Submaximal = True Then
PeakVO2 = SS208Int + ((TCM + (TCS / 60)) * SS208TC) + ((BW *
0.453592) / ((((HF * 12) + HI) * 0.0254) ^ 2) * SS208BMI)
End If

'score is diplayed in appropriate cell
Cells(14, 3) = PeakVO2

n = Sheets("Equations").Cells(19, 14)

'output data from subject into database
Sheets("Data").Cells(2 + n, 1) = (n + 1)
Sheets("Data").Cells(2 + n, 2) = A
Sheets("Data").Cells(2 + n, 3) = (HF * 12) + HI
Sheets("Data").Cells(2 + n, 4) = ((HF * 12) + HI) * 2.54
Sheets("Data").Cells(2 + n, 5) = BW
Sheets("Data").Cells(2 + n, 6) = BW * 0.453592
Sheets("Data").Cells(2 + n, 7) = (BW * 0.453592) / ((((HF * 12) + HI)
* 0.0254) ^ 2)
Sheets("Data").Cells(2 + n, 8) = TCM + (TCS / 60)
Sheets("Data").Cells(2 + n, 9) = PeakVO2
If Stairmill = True Then
Sheets("Data").Cells(2 + n, 10) = "Stairmill"
ElseIf Treadmill = True Then
Sheets("Data").Cells(2 + n, 10) = "Treadmill"
End If
If Maximal = True Then
Sheets("Data").Cells(2 + n, 11) = "Maximal"
ElseIf Submaximal = True Then
Sheets("Data").Cells(2 + n, 11) = "Submaximal"
End If

Unload VO2Input

End Sub



Option Explicit

Private Sub Enter_Click()

MaxVO2Predict.A = InputAge.Value
MaxVO2Predict.HF = InputHeightFeet.Value
MaxVO2Predict.HI = InputHeightInches.Value
MaxVO2Predict.BW = InputWeight.Value
MaxVO2Predict.TCM = InputTestTimeMin.Value
MaxVO2Predict.TCS = InputTestTimeSeconds.Value
MaxVO2Predict.Treadmill = Tread.Value
MaxVO2Predict.Stairmill = Stair.Value
MaxVO2Predict.Maximal = Max.Value
MaxVO2Predict.Submaximal = Submax.Value

VO2Input.Hide

End Sub

Private Sub Quit_Click()

Unload VO2Input
End

End Sub

 
Answer #9    Answered By: Jackson Williams     Answered On: Mar 09

This code

Set VO2Input = New VO2Input
VO2Input.Show

is not desirable. For a start, your variable  should have a different name
to your form. But, if VO2Input is a form defined in the VBE, then it
already exists, and you don't need a new copy of it. Simply show the
original one.

Your specific error message is going to be based on the naming confusion
between your variable VO2Input and your form VO2Input, or possibly on the
fact that you have two unloads of VO2Input and they might both be being
executed.

Try removing the VO2Input variable and the "new" statement.

You also don't actually ever need to unload the form - just hide it.

 
Didn't find what you were looking for? Find more on custom dialogue box variable entry trouble Or get search suggestion and latest updates.




Tagged: