how to carry variable to another module

Jake975

Board Regular
Joined
Feb 9, 2020
Messages
73
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Jake975

Board Regular
Joined
Feb 9, 2020
Messages
73
Office Version
  1. 2016
Platform
  1. Windows
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
 

richh

Board Regular
Joined
Jun 24, 2007
Messages
107
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)
 

Jake975

Board Regular
Joined
Feb 9, 2020
Messages
73
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,954
Members
410,713
Latest member
TaremyLunsil
Top