VBA msgbox in Form code, get variable from Module sub

Question

In the USER FORM code, after the user has clicked "OK," there is a msgbox ie. MsgBox ("The total price is " & Price & "")

The thing is, I've calculated the Price variable in a MODULE sub.

How can I ensure the price value actually shows up? How do I connect a variable from a module sub to a form?

To be more general, how can I make a message box to show up after that the user clicked "OK". Do I need to have two message boxes within my code?

Code Sample:

In Module:

Dim Price As Currency

Sub Test1()
    Price = wsSheet.Range("A1").End(xlDown).Value
End Sub

In Form:

Sub cmdOK_click
   MsgBox ("The total price is " & Price & "")
End Sub

I would make the Test1 a Public Sub and also call it on the condition that user clicks OK. That's an idea and actually it's too late for me to check it. But let us know if that would work for you. — M--
– M--, Commented Apr 3, 2017 at 4:29
is Test1() Sub called within the same Sub that calls the User Form? — user3598756
– user3598756, Commented Apr 3, 2017 at 6:20

J. L. Muller · Accepted Answer · 2017-04-03 12:42:08Z

1

All indicates you are not calling the Sub where you perform the Price calculation. I Suggest you to do like this:

In your module:

Public Price As Currency

Sub Test1()

    Price = wsSheet.Range("A1").End(xlDown).Value

End Sub

In your Userform code:

Private Sub cmdOK_click()

    Call Test1
    MsgBox ("The total price is " & Price & "")

End Sub

If your plans for the code allows, you can also make the MsgBox shows up in the module instead. So you'd have something like this:

In your module:

Public Price As Currency

Sub Test1()

    Price = wsSheet.Range("A1").End(xlDown).Value
    MsgBox ("The total price is " & Price & "")

End Sub

In your Userform code:

Private Sub cmdOK_click()

    Call Test1

End Sub

Please, let me know if it helps you or not properly.

answered Apr 3, 2017 at 12:42

J. L. Muller

3071 gold badge4 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1