Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

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.

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

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.

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.

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

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.

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.

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

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.

Related Topics:

- Variable scope & Lifetime limits causing trouble
- How to display PRINT DIALOG BOX (?)
- Named formula/Windows dialog boxes
- Client side message (dialog) box
- i want a dialog box to pop out when user click the mouse
- excel vba word dialog box problem
- Dialog Box question
- combo box/list box/checkbox Tool Tip
- Fill List Box Based On Selection On Other List Box
- what is instance variable and class variable.
- What variables are in heap and what variables are in stack
- Black box - White box testing
- multiplying two integer variables and assigning to a long variable
- How to extract unique entries from a particular column in a range
- Opening DropDown fields on Entry?
- If not found then data entry
- VBA Code - how to create an entry to registry & write to it and rea
- data entry macro
- Tomcat Showing up 2 entries in the registry
- data entry macro
- how to create an al program that matches several entries using weight
- trouble with IF/OR, Left and a simple loop
- Trouble paging a datagrid
- trouble with playlist
- having trouble with lab