Pull data from an Array in a private sub and use it in another private sub

Question

I'm creating a document control program.

When the "Add Update" button is pressed in the sheet I want to store the existing data in an array and put the new data in its place on sheet.

Then after the "Show logs" button is pressed the contents of the array mentioned above is shown to the user.

Private Sub CommandButton1_Click()

'Lets say this is the array holding the data in it
example_array = Array("String1", "String2")
'I want to use the contents of the example_array in CommandButton2

UserForm1.Show

End Sub


Private Sub CommandButton2_Click()

MsgBox (example_array(0))
'So here I want to MsgBox to show to user "String1"

End Sub

I do not know why I use Private Subs. I created the buttons Using Insert-ActiveX-Button and it automatically added Private Subs.

I tried making the example_array global by using

Public example_array(1) As String`
example_array = Array("String1","String2")

Answer 1

• CommandButton1_Click() and CommandButton2_Click() should be in the worksheet module. Private isn't an issue.

Private Sub CommandButton1_Click()
    example_array = Array("String1", "String2")
    UserForm1.Show
End Sub
Private Sub CommandButton2_Click()
    MsgBox example_array(0)
End Sub

• Define the public variable in a standard module ( VBE menu Insert>Module ) as below

Public example_array As Variant