Contorl Array

naishpatel · Apr 29th, 2002, 08:38 AM

Hi all,

I am creating list of command buttons on one of the worksheet of excel sheets. On click event of each button, it will direct to corresponding worksheet. Can i create control array of command buttons and assign name to them dynamically. Please advise.

[ This Message was edited by: naishpatel on 2002-04-29 07:45 ]

Mark O'Brien · Apr 29th, 2002, 08:45 AM

Control Arrays aren't supported in VBA.

There may be a smart way around this limitation if you want to give an example of your needs.

naishpatel · Apr 29th, 2002, 08:54 AM

Thank you Mark.

I have one worksheet which have all data. Based on that i am reading a column of that sheet and creating worksheet for each cell value. Each worksheet created this way will have data for that corresponding value entity. So i need to create list of buttons next to each parent worksheet cell which will direct user to the corresponding worksheet. Is there any way to implement this functionality in dynamic fashion.

Thank you

Mark O'Brien · Apr 29th, 2002, 09:03 AM

Wow, nice idea. Have you created any code to do any of this yet? Adding the buttons programmatically shouldn't be a problem, and placing them in the desired position shouldn't be too bad. My suggestion for the buttons though (in lieu of a control array) is to name the buttons after the sheets that they are a "shortcut" to. This could actually lead to some really efficient code, since it may be possible to avoid putting a click event for each button.

If you are familiar with class modules, there may be a good solution using those. I just knocked up an example for someone yesterday using class modules to store "Forms" commandbuttons. (I'm in work just now or I would send it to you, I can do a little bit more work on it tonight to make it more relevant to your needs)

naishpatel · Apr 30th, 2002, 06:31 AM

Thank you once again Mark. I have one question about creating class module in VBA. Can i create an array to store names of command buttons while creating them dyanamically. And can i set ******* event macro(sub routines) dyanamically while creating them. I will write a sub routine in class module (that will accept a worksheet name as a string variable) for directing user to corresponding worksheet and i guess that sub routine can be accessed by any public function. Please advise.

[ This Message was edited by: naishpatel on 2002-04-30 05:32 ]

Apr 29th, 2002, 08:45 AM	#2
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	Control Arrays aren't supported in VBA. There may be a smart way around this limitation if you want to give an example of your needs. __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 29th, 2002, 08:38 AM	#1
naishpatel New Member Join Date: Apr 2002 Location: Nari Posts: 6	Hi all, I am creating list of command buttons on one of the worksheet of excel sheets. On click event of each button, it will direct to corresponding worksheet. Can i create control array of command buttons and assign name to them dynamically. Please advise. [ This Message was edited by: naishpatel on 2002-04-29 07:45 ]

Apr 29th, 2002, 08:54 AM	#3
naishpatel New Member Join Date: Apr 2002 Location: Nari Posts: 6	Thank you Mark. I have one worksheet which have all data. Based on that i am reading a column of that sheet and creating worksheet for each cell value. Each worksheet created this way will have data for that corresponding value entity. So i need to create list of buttons next to each parent worksheet cell which will direct user to the corresponding worksheet. Is there any way to implement this functionality in dynamic fashion. Thank you

Apr 29th, 2002, 09:03 AM	#4
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	Wow, nice idea. Have you created any code to do any of this yet? Adding the buttons programmatically shouldn't be a problem, and placing them in the desired position shouldn't be too bad. My suggestion for the buttons though (in lieu of a control array) is to name the buttons after the sheets that they are a "shortcut" to. This could actually lead to some really efficient code, since it may be possible to avoid putting a click event for each button. If you are familiar with class modules, there may be a good solution using those. I just knocked up an example for someone yesterday using class modules to store "Forms" commandbuttons. (I'm in work just now or I would send it to you, I can do a little bit more work on it tonight to make it more relevant to your needs)

Apr 30th, 2002, 06:31 AM	#5
naishpatel New Member Join Date: Apr 2002 Location: Nari Posts: 6	Thank you once again Mark. I have one question about creating class module in VBA. Can i create an array to store names of command buttons while creating them dyanamically. And can i set ******* event macro(sub routines) dyanamically while creating them. I will write a sub routine in class module (that will accept a worksheet name as a string variable) for directing user to corresponding worksheet and i guess that sub routine can be accessed by any public function. Please advise. [ This Message was edited by: naishpatel on 2002-04-30 05:32 ]