User form to print an array of sheets

0 votes

Please enlighten me as to why the for loop causes me to lose the array when it is placed in the UserForm Initialize sub. If I position Unload Me underneath PrintOut, Me will take its place. The array is successfully passed with hide enabled. Yet, even with Unload Me at the beginning, as demonstrated below, rpvSheet is passed appropriately.

''Declare variables
    Dim recapArr() As Long, i As Long, j As Long, rpvSheet as Long

Private Sub CommandButton1_Click()
    Unload Me
''Print preview
    If CheckBox2 = True Then Worksheets(recapArr).PrintOut Copies:=TextBox1.Value, Preview:=True
    If CheckBox3 = True Then Worksheets(rpvSheet).PrintOut Copies:=TextBox1.Value, Preview:=True
End Sub

Private Sub CommandButton2_Click()
    Unload Me
''Direct print
    If CheckBox2 = True Then Worksheets(recapArr).PrintOut Copies:=TextBox1.Value
    If CheckBox3 = True Then Worksheets(rpvSheet).PrintOut Copies:=TextBox1.Value
End Sub

Private Sub UserForm_Initialize()
''Check for hidden sheets that will not print
    j = 0
    For i = 3 To 4
        If Sheets(i).Visible = True Then
            ReDim Preserve recapArr(j)
            recapArr(j) = Sheets(i).Index
            j = j + 1
        End If
    Next i
    If Sheet5.Visible = True Then rpvSheet = 5
    If Sheet6.Visible = True Then rpvSheet = 6
End Sub
Apr 3, 2023 in Others by Kithuzzz
• 38,000 points
402 views

1 answer to this question.

0 votes
It appears that when a UserForm is unloaded, reference types are lost. Why would you depend on variables in a closed object? Move Unload Me after the print jobs if you require the array. Instead, declare the array variable inside a module. Me. The UserForm is not closed by hiding. The array endures because of this. I hope this helps you.
answered Apr 3, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Reducing sequences in an array of strings

I've written a C# app to solves ...READ MORE

answered Nov 2, 2018 in Others by DataKing99
• 8,250 points
923 views
0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
679 views
0 votes
1 answer

How to store an array in localstorage?

Localstorage only supports Strings. So you can ...READ MORE

answered Jul 1, 2019 in Others by sunshine
• 1,300 points
23,084 views
+1 vote
1 answer

Between cyber security and CCNA profession which one is best in terms of time to become an expert and salary payment

CCNA professional is more inclined towards the ...READ MORE

answered Dec 18, 2019 in Others by Pri
2,084 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,260 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,697 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
975 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,090 views
0 votes
1 answer

Excel worksheet multi-criteria function (like Index-Match array) to fetch last value of an item, looking up by item name and date

Use: =SUMIFS(C:C,B:B,E12,A:A,MAXIFS(A:A,B:B,E12)) It will return the value at the ...READ MORE

answered Apr 6, 2023 in Others by narikkadan
• 63,600 points
819 views
0 votes
1 answer

How to maximize SEO of an internationalized website?

there is no single answer to SEO ...READ MORE

answered Feb 11, 2022 in Others by narikkadan
• 63,600 points
576 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP