Command Button

cksm4 · #1 02-03-2011, 07:28 AM

Hello,

I have a quick part that inserts a table that includes a command button. The problem is that the new command button gets renamed if i already exists (i.e. CmdDoAction1 gets renamed CmdDoAction2... and so on). Now the on click code for CmdDoAction1 will not run for CmdDoAction2. The only solution I can think of is using a macro that runs when all command buttons are pressed and then focusing on the name of the button. Any ideas?

Thanks!

Brock

macropod · #2 02-03-2011, 03:15 PM

Hi Brock,

Each command button will need its own macro, but all this will need is code to call the larger macro to do the real work. For example:

Code:

Private Sub CmdDoAction1_Click()
Call MySub
End Sub

If you know how many of these could potentially be needed, you could simply build your code with them ahead of time. They'll remain dormant until the corresponding command button gets created.

cksm4 · #3 02-17-2011, 12:11 PM

The problem I am having is that I have no idea a head of time how many will be needed. I think I am just pushing the limits of command buttons here

macropod · #4 02-25-2011, 02:04 AM

Hi Brock,

I've been away for a few weeks. Hence the delay in replying.

If there's a reasonable upper limit, you could pre-insert the relevant macros as suggested in my previous post. They'll simply remain dormant until the corresponding command buttons get created.

cksm4 · #5 02-26-2011, 09:52 AM

Hello Paul,

No worries... the notification e-mail of replies started going to my spam folder so I fell behind anyhow.

I am still working with this activities idea were the user adds activities to the form. Each activity is basically a table with content controls that need to be filled out. Each activity has the command button which deletes the activity (table). I will have no idea how many activites the user will need. There could be 1 or 100. Since each activity is a quick part, they are basically a copy of the first activity. So each command button will have the same base name.

Stessing that I am still new to VBA, I am guessing that you are saying to create code that runs on all command buttons. And from this run actions based on the name of the command button selected. So if the name of this button that is repeated in each activity is MyCommandButton, then the code would refer to the button as Like MyCommandButton*. Right/wrong?

Thanks!
Brock

macropod · #6 02-26-2011, 12:50 PM

Quote:

Originally Posted by cksm4

I will have no idea how many activites the user will need. There could be 1 or 100. Since each activity is a quick part, they are basically a copy of the first activity. So each command button will have the same base name.

Stessing that I am still new to VBA, I am guessing that you are saying to create code that runs on all command buttons. And from this run actions based on the name of the command button selected. So if the name of this button that is repeated in each activity is MyCommandButton, then the code would refer to the button as Like MyCommandButton*. Right/wrong?

Hi Brock,

As you create each new command button via Quick Parts, the button number for the new button should increment. They'll get the default command button names - starting at CommandButton11! So you'll end up with CommandButton1, CommandButton11, CommandButton12, CommandButton13 and so on. And they'll all have the same caption that you gave the Quick Parts command button. So you'll need subs corresponding to each of these:

Code:

Private Sub CommandButton1_Click()
Call MySub
End Sub
 
Private Sub CommandButton11_Click()
Call MySub
End Sub 
 
Private Sub CommandButton12_Click()
Call MySub
End Sub

etc, and, in the same module:

Code:

Private Sub MySub()
'the generic code that does the real work.
End Sub

Replicating the command button code for as many as you're likely to need is pretty much a copy/paste operation, with edits to the command button numbers.

While all of the above is feasible - and fairly easily done - I'm starting to wonder if you wouldn't be better off having a small userform with just a single command button floating over the document, rather than optentially having a plethora of ActiveX buttons in the document as a result of being replicated by Quick Parts. That way, there'd be no concerns over how many elements of your document might be created via Quick Parts. The generic macro could be coded to work with whatever range in the document the user has selected (or some other range, if you prefer).

cksm4 · #7 02-26-2011, 01:12 PM

Hey Paul,

Thanks for the quick reply. I am trying to avoid the userform as then the user, as you noted, will have to select the range. If there is no way to call one sub for all command buttons (and then run code on the default name plus a wildcard, i.e. MyCOmmandButton*), then the option you suggested will work for me and I will use it. Thanks for the help again! Much appreciated!

Brock

macropod · #8 02-27-2011, 08:47 PM

Hi Brock,

Quote:

I am trying to avoid the userform as then the user, as you noted, will have to select the range.

No, a user-selected range isn't the only option. You might be able to determine the range to act on programmatically, independently of what the user might have selected, or use the user's selection as a reference point from which to programmatically determine the range to act on (eg by reference to the page # or document Section that the selection point is in). Without knowing your requirements, it's hard to say what the best approach might be.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Where is the undo command	Codey	Excel	1	06-15-2010 01:10 PM
Using the LOOKUP Command	Grapejuice	Excel	2	10-15-2008 02:02 PM
Add-In:How to add command right click command bar	phang	Outlook	0	01-15-2007 02:53 AM
command buttons	ronf	Excel	0	04-28-2006 08:32 AM
command buttons	ronf	Excel	0	12-03-2005 06:26 AM