how to carry variable to another module

Jake975

Board Regular
Joined
Feb 9, 2020
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
I took the advice of a user and reworked my module so it only contains the prompts needed. This is just the start. But I noticed the variable s is not carrying over from the user form button clicks. When I call the macro it runs and fills everything out as it should but after click "yes" s=1 but is i restart this macro it is still saying s=0.
Also how can I loop this macro so when s"value" is changed the information is changed with out crashing excel?
VBA Code:
Sub ProcessnaTEST()
 Dim s As Integer
Dim Prompt As String
'DO"I tried to start the loop here"
Select Case s
Case Is = 0
        Prompt = NightAuditP.ShowMsg("Are you ready to start the night audit file process?" & vbNewLine _
        & "This is for the date of: " & Format(Date - 1, "mm-dd-yyyy"), NightAuditP.Previous.Visible = False, Button2Text:="Yes")
Case Is = 1
        Prompt = NightAuditP.ShowMsg("Have you saved the" & vbNewLine & "Adjustment Log" & vbNewLine _
        & " & " & vbNewLine & "Daily Cash Log?", , Button1Text = "Back", Button2Text = "Next")
Case Is = 2
        Prompt = NightAuditP.ShowMsg("Please save all required documents to complete final packet." & vbNewLine _
        & "Click OK once completed.", , Button1Text = "back", Button2Text = "next")

End Select
'loop"and end it here but it caused excel to freeze
End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
I figured out the s reference. I had to reference the whole name like this:
s = VBAProject.NightAuditP.s
I still need help with the loop part though
Please and thanks for your time
 
Upvote 0
You can pass to the sub by reference.

Change the sub call to

VBA Code:
Call processnaTEST(s)
'Change sub declaration to

sub processnaTEST(s as Integer)
 
Upvote 0
thanks I had got the variable working with the full name and I cant do it the way your suggestion because the variable is changed by the userform click so I have to use the whole name so i can rerun the macro so the information can be changed.
thanks for the suggestion i will keep it in mind for the future.
Im currently stuck on the looping part so it doesn't freeze excel. if you have any suggestions on that it would be a huge help.
 
Upvote 0

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top