Passing collection to comand button

[TudorM]

Hi all ,

Could someone help me please with achieving following :
I have :

1.subroutine:
-inside code....
then i have 2 new collections inside wich i passed from other subroutine by calling the first one .

The problem is i want to pass this 2 collections to a command button,see picture attached.

what i am trying to say is , only after i press the button those 2 collection to pass to other subroutine.

Will appreciate any suggestion,thank you!

 

[IDS]

It isn't clear to me what you are trying to do. Could you list (in text, in the message) the steps you want the code to follow.

Also note that you can post code in the message, using the "Code" icon above the edit box.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Agent666]

What's the scope of nwcol2 & nwcol. If you're hoping to press a button on the sheet, you'll need to initialise those variables within the button code. Unless they have a global or public scope and they have already been initialised, might work then.

Hard to say because the code is incomplete in that you have not provided how those variables are initialised, only a snippet provided at the top of your screenshot.

 

[TudorM]

Hi all ,

Thank you for your support.
The code is a long one , but i just made a simple one for you to understand what i want to achieve :

Public Sub CommandButton1_Click()

Dim a, b, c As String

a = "hi"
b = "hope you are doing well"
c = "thank you for help"

ListBox1.AddItem a
ListBox1.AddItem b
ListBox1.AddItem c

Dim nwcol As Collection
Set nwcol = New Collection

nwcol.Add (a & b & c)

End Sub

Public Sub CommandButton2_Click(ByRef nwcol As Collection)

ListBox2.AddItem nwcol.Item(1)

End Sub

So you can see that i want to pass the nwcol to second button and to be listed in listbox2 , but only when second button will be pressed.
Many thanks!

 

[IDS]

Options are:

1) Copy the CommandButton1_Click code to a function that returns nwcol. You can then call that from either Button1 or Button2.

2) Write nwcol to a named spreadsheet range. You can then read that in the CommandButton2_Click code.

3) As Agent666 suggested, make nwcol a global variable. It will than be available inside CommandButton2_Click.

As far as I know you can't pass arguments to routines called from a command button.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[TudorM]

Thank you for suggestions !