variable ref

Jake975

Board Regular
Joined
Feb 9, 2020
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Private Sub UserForm_Initialize()
Select Case iStepCurrent
Case 6
    Call CK1
Case 15
    Call CK2
End Select
If ListBox1.ListCount = Empty Then
    Me.Cleared.Enabled = True
    End If
End Sub
this macro is used in userform b
but i need it to reference the " iStepCurrent" variable from another userform so it displays the correct information
what am i doing wrong of it is already declared at the top of the module as
Private iStepCurrent As Integer
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,026
Office Version
  1. 2016
Platform
  1. Windows
If you declare iStepCurrent as Public in one UserformA's module, you can refer to it in the other UserformB's as

UserfomA.FoundRow

If you declare the iStepCurrent as Public in a standard module, you can simply refer to it by its name directly
iStepCurrent

Declaring as Private will only refer iStepCurrent the within the same Userform module
 

Jake975

Board Regular
Joined
Feb 9, 2020
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
thanks i realized I had them backwards so i up dated it to
VBA Code:
Private Sub UserForm_Initialize()
iStepCurrent = NightAuditP.iStepCurrent
Select Case iStepCurrent
Case 6 Or 7
    Call CK1
Case 15
    Call CK2
End Select
If ListBox1.ListCount = Empty Then
    Me.Cleared.Enabled = True
    End If
End Sub
but why would I have to relaunch the form again before it displays the correct information?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,026
Office Version
  1. 2016
Platform
  1. Windows
thanks i realized I had them backwards so i up dated it to
VBA Code:
Private Sub UserForm_Initialize()
iStepCurrent = NightAuditP.iStepCurrent
Select Case iStepCurrent
Case 6 Or 7
    Call CK1
Case 15
    Call CK2
End Select
If ListBox1.ListCount = Empty Then
    Me.Cleared.Enabled = True
    End If
End Sub
but why would I have to relaunch the form again before it displays the correct information?
You meant you need to refresh ListBox? You can create a refresh subroutine and trigger it when clicking on any object on Userform perhaps.
 

Jake975

Board Regular
Joined
Feb 9, 2020
Messages
101
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Im not sure how to make that happen this is the code that makes the selection
VBA Code:
Private Sub UserForm_Initialize()
iStepCurrent = NightAuditP.iStepCurrent
Select Case iStepCurrent
Case 6 Or 7
    Call CK1
Case 15 Or 16
    Call CK2
End Select
If ListBox1.ListCount = Empty Then
    Me.Cleared.Enabled = True
    End If
End Sub
the form starts blank and if i click retry it relaunches the form with this code
VBA Code:
Private Sub retry_Click()
Unload Me
Missing.Show
End Sub
once it is reloaded it displays the correct information
but im not sure why it isnt showing up right the first time
is there something else i can try?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,026
Office Version
  1. 2016
Platform
  1. Windows
I don't know how you populate the UserformB. As I understand it, the iStepCurrent determined the correct list in UserformB List.

I guess Call CK1 or Call CK2 populate the list, right? What you need to do is after you change iStepCurrent in UserformA, you can change event (for example) in UserformA to execute Call CK1 or Call CK2 to update the list again. The list was not updated because the Call CK1 or Call CK2 is executed only when the UserformB is loaded only (UserForm_Initialize())
 

Jake975

Board Regular
Joined
Feb 9, 2020
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
I got it to display the correct information by calling the macro first then the macro first then the macro calls the userform. Now I have this issue that the close button is active despite the list being populated and if i click retry the form refreshes right
 

Jake975

Board Regular
Joined
Feb 9, 2020
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Dim ufDic As Object
Private Const cStepCount    As Integer = 27      ' <<< step 0 is start of process and should not be counted
Public iStepCurrent        As Integer
Private siProgressPart      As Single
Private siProgressALL       As Single


Function GetFiles(sPath As String) As Variant
    Dim sFileName As String
    With CreateObject("Scripting.Dictionary")
        sFileName = Dir(sPath, vbNormal)
        Do While Not sFileName = vbNullString
            .Item(sFileName) = Empty
            sFileName = Dir
        Loop
        GetFiles = .Keys
    End With
End Function



Private Sub Cleared_Click()
Unload Me
NightAuditP.Show
End Sub

Private Sub ListBox1_Click()
   Me.Label2.Caption = ufDic(Me.ListBox1.Value)
   Me.TextBox1 = "Save Reports to this address:" & vbNewLine & "G:\Shared drives\437 - HIE Malvern - All Users\Front desk\Night Audit\Audit Reports\Disembodied\" & Format(Date - 1, "mm-dd-yyyy")

End Sub

Private Sub retry_Click()
Unload Me
NightAuditP.Show
Missing.Show

End Sub

Private Sub Scan2_Click()
Call scn
Me.Scan2.Visible = False
NightAuditP.Scan.Visible = False

End Sub

Private Sub UserForm_Activate()

iStepCurrent = NightAuditP.iStepCurrent
Select Case iStepCurrent
Case 15 Or 16
    Call Me.CK2
Case 6 Or 7
    Call CK1

End Select

If Me.ListBox1.ListCount > 0 Then
 Me.Cleared.Enabled = False
   End If

End Sub
Sub CK1()
    Dim DirectoryListArray As Variant, sPath As String
    Dim rg As Range, i As Long, j As Long
    sPath = ""

    DirectoryListArray = GetFiles(sPath)
    
    Set rg = Worksheets("Data List").Cells(1, 1).CurrentRegion
    Set ufDic = CreateObject("Scripting.Dictionary")
    For j = 2 To rg.Rows.Count
       If Not rg.Cells(j, 6) = vbNullString Then
           If UBound(Filter(DirectoryListArray, rg.Cells(j, 6), 1, 1)) < 0 Then
               ufDic.Item(rg.Cells(j, 3).Value) = rg.Cells(j, 8).Value
           End If
       End If
    Next j
  
  Me.ListBox1.List = ufDic.Keys          'Adapt to your needs

Me.Show
End Sub
Sub CK2()
    Dim DirectoryListArray As Variant, sPath As String
    Dim rg As Range, i As Long, j As Long
    
sPath = ""
    DirectoryListArray = GetFiles(sPath)
    
    Set rg = Worksheets("Data List").Cells(1, 1).CurrentRegion
    Set ufDic = CreateObject("Scripting.Dictionary")
    For j = 2 To rg.Rows.Count
       If Not rg.Cells(j, 7) = vbNullString Then
           If UBound(Filter(DirectoryListArray, rg.Cells(j, 7), 1, 1)) < 0 Then
               ufDic.Item(rg.Cells(j, 3).Value) = rg.Cells(j, 8).Value
           End If
       End If
    Next j
    
  Me.ListBox1.List = ufDic.Keys          'Adapt to your needs
Me.Show
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
End If
End Sub
I fixed the error when i have to refresh before it displays the right information and that also fixed the error with the button but with the first time around it works right i can click retry as many times as i want with no issue but the second case it loads right but if i click retry it dosent load right im not sire why?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,315
Members
416,239
Latest member
Counselor85027

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
Top