PDA

View Full Version : Create a working CommandButton in a Userformusing VBA Code



vodkasoda
03-05-2009, 08:57 AM
Hi all, I have a Userform that is designed as part of my Excel VBA program.

I want to Add a variable number of CommandButtons to the Userform when the program is running.

I can get the UserForm looking exactly how I want but cannot get the CommandButtons to actually *do* anything !!! When I click on them they just do nothing. I added MsgBoxes to check their names and they are called CommandButton1, CommandButton2, etc., so I added CommandButton1_Click() modules to both the UserForm Code and to the Module Code, but still nothing happens.

If I use the .OnAction command I get a Run Time Error 438.

I therefore set up a Dummy CommandButton on the UserForm at Design time & made it invisible. I thought I would be able to Select / Copy this CommandButton & then Paste versions of it on the UserForm adjusting it's Position & Caption as desired, but now I can find no way to do this !!! If I am in design mode & click on a CommandButton, then Copy & Paste it, ALL the Properties are cloned in the new CommandButton ... is there a way of doing this in my VBA code ?!?

I have found a way of getting CommandButtons to work if the UserForm is created temporarily during the run & not created as part of the original design (strangely, I am not allowed to include the link here :dunno !!!) but I can't find a way of tweaking it to work when the UserForm already exists.

Can anybody help, I am all Googled out :help !!!

lucas
03-05-2009, 09:00 AM
To quote Bob(XLD) who I agree with on this:


Why would you want to add controls via code. It is not a good idea, and incredibly inefficient.


http://www.vbaexpress.com/forum/showthread.php?t=11177

Bob Phillips
03-05-2009, 09:12 AM
What you could do is to create a control array for all of the added buttons and route through a class handler.

This is an example of the form code



Dim mcolEvents As Collection
Dim cBtnEvents As clsUserFormEvents

Private Sub cmdOK_Click()
Dim newButton As msforms.Control

Set newButton = Me.Controls.Add("Forms.CommandButton.1")
newButton.Left = 12
newButton.Top = 30
newButton.Caption = "newCmd1"
Set cBtnEvents = New clsUserFormEvents
Set cBtnEvents.mButtonGroup = newButton
mcolEvents.Add cBtnEvents

Set newButton = Me.Controls.Add("Forms.CommandButton.1")
newButton.Left = 48
newButton.Top = 30
newButton.Caption = "newCmd2"
Set cBtnEvents = New clsUserFormEvents
Set cBtnEvents.mButtonGroup = newButton
mcolEvents.Add cBtnEvents

End Sub


and then this is the class code with skeltal processing logic



Public WithEvents mButtonGroup As msforms.CommandButton

Private Sub mButtonGroup_Click()
MsgBox mButtonGroup.Caption & " has been pressed"
End Sub

Bob Phillips
03-05-2009, 09:13 AM
To quote Bob(XLD) who I agree with on this:



http://www.vbaexpress.com/forum/showthread.php?t=11177


Oops! And then look what I did!

vodkasoda
03-05-2009, 09:16 AM
My reason for doing it this way ? It seems the most logical !!!

I am picking up data from a Sheet that is currently 29 Rows, but will continue to grow & I am placing that data into my Userform with a scroll bar. The user will have the option of Sorting and/or Filtering the data at a later date, but for now I just want him to be able to see, and act upon, his data.

As far as I know, and I don't know a lot of VBA:( , if I create the UserForm at design time I am restricted to the size of the Window that I can see ...

vodkasoda
03-06-2009, 02:43 AM
... but it clearly shows just how little I understand VBA !!!

I always use Option Explicit & I always code all of my variables as "Public", in a module called "Definitions" ... but if I try to do that here I get an error message on the "Public WithEvents" line saying "Only valid in Object module"" ... in fact I get the same error wherever I put this line !!!

I also get an error "User-defined type not defined" when I use the line ...Dim cBtnEvents As clsUserFormEvents

:(




What you could do is to create a control array for all of the added buttons and route through a class handler.

This is an example of the form code



Dim mcolEvents As Collection
Dim cBtnEvents As clsUserFormEvents

Private Sub cmdOK_Click()
Dim newButton As msforms.Control

Set newButton = Me.Controls.Add("Forms.CommandButton.1")
newButton.Left = 12
newButton.Top = 30
newButton.Caption = "newCmd1"
Set cBtnEvents = New clsUserFormEvents
Set cBtnEvents.mButtonGroup = newButton
mcolEvents.Add cBtnEvents

Set newButton = Me.Controls.Add("Forms.CommandButton.1")
newButton.Left = 48
newButton.Top = 30
newButton.Caption = "newCmd2"
Set cBtnEvents = New clsUserFormEvents
Set cBtnEvents.mButtonGroup = newButton
mcolEvents.Add cBtnEvents

End Sub


and then this is the class code with skeltal processing logic



Public WithEvents mButtonGroup As msforms.CommandButton

Private Sub mButtonGroup_Click()
MsgBox mButtonGroup.Caption & " has been pressed"
End Sub

vodkasoda
03-06-2009, 02:46 AM
As part of my original question, I asked this :

If I am in design mode & click on a CommandButton, then Copy & Paste it, ALL the Properties are cloned in the new CommandButton ... is there a way of doing this in my VBA code ?!?

If this is possible, surely it's the easiest way around all my problems, but nobody seems to have picked up on it ... maybe because it's not possible, but I'd like to find out, all the same ...

Bob Phillips
03-06-2009, 03:04 AM
As part of my original question, I asked this :

If I am in design mode & click on a CommandButton, then Copy & Paste it, ALL the Properties are cloned in the new CommandButton ... is there a way of doing this in my VBA code ?!?

If this is possible, surely it's the easiest way around all my problems, but nobody seems to have picked up on it ... maybe because it's not possible, but I'd like to find out, all the same ...

No!

Bob Phillips
03-06-2009, 03:05 AM
... but it clearly shows just how little I understand VBA !!!

I always use Option Explicit & I always code all of my variables as "Public", in a module called "Definitions" ... but if I try to do that here I get an error message on the "Public WithEvents" line saying "Only valid in Object module"" ... in fact I get the same error wherever I put this line !!!

I also get an error "User-defined type not defined" when I use the line ...Dim cBtnEvents As clsUserFormEvents

:(

That second block of code that I provided should be in a class module, named clsUserFormEvents - look at the code.

vodkasoda
03-06-2009, 03:31 AM
That's a pity ... perhaps it's a bit too straightworward :eek: !!!


No!

vodkasoda
03-06-2009, 03:34 AM
OK ... I'd never heard of a "class module" before today ... in what way is it different to a standard "module" ?

Anyhow, I have created it & when I try to rename it from "Class1" to "clsUserFormEvents " I get a pop-up error message saying "Not a legal object name" ...

Thanks for your help, btw, I may seem a little cynical at times, but it is appreciated :thumb ...



That second block of code that I provided should be in a class module, named clsUserFormEvents - look at the code.

Bob Phillips
03-06-2009, 04:05 AM
You don't seem at all cynical, nothing to worry about at all.

I have had that error, but it usually happens when I try to rename a module whilst I have a bit of code in process.

Attached is an example.

vodkasoda
03-06-2009, 04:54 AM
Yes, my problem was exactly that, the program was running but was in a paused state ... shame they can't give you an error message telling you that, rather than one that is so misleading (see, I'm getting cynical :yes ) !!!

But seriously, I am getting somewhere, thank you ... the following code gives me a scrolling list of Command Buttons, exactly as I want ...

MyTop = 2
For A = 1 To MyUniqueNo
MyLeft = 14
UserForm3c.Frame1.ScrollBars = fmScrollBarsVertical
UserForm3c.Frame1.ScrollHeight = UserForm3c.Frame1.Height * (MyUniqueNo / 18)
Set MyCommandButton = UserForm3c.Frame1.Controls.Add("Forms.CommandButton.1")
With MyCommandButton
.Enabled = True
.Caption = A
.Width = 42
.Height = 18
.Left = MyLeft
.Top = MyTop
End With
Set cBtnEvents = New clsUserFormEvents
Set cBtnEvents.mButtonGroup = MyCommandButton
mcolEvents.Add cBtnEvents
MyTop = MyTop + 18
MyLeft = MyLeft + 42
Next
UserForm3c.Show

... but ONLY if I comment out the line

mcolEvents.Add cBtnEvents ... and also, the only Command Button that reacts to being clicked is the very last one ...

So, question 1, why does this line need commenting out (error message says "Run Time error 91 - Object variable or With block variable not set"), what is wrong ?

And question 2, as this erroneous line is to do with a "Collection", is it the reason why only the last Command Button is acting as I'd like ?

I'm sorry, I do try to solve these things on my own before & after I respond, but I've never used code like this before, I have no idea what a "Collection" is !!!



You don't seem at all cynical, nothing to worry about at all.

I have had that error, but it usually happens when I try to rename a module whilst I have a bit of code in process.

Attached is an example.

Bob Phillips
03-06-2009, 04:57 AM
What is a scrolling list of Commnad Buttons, and where does that code go in this example.

vodkasoda
03-06-2009, 05:21 AM
I have incorporated your code into my program to see if it will do what I want, and it looks like it will work if I can just figure our what the problem with that line is.

I have a Userform with a frame in it. It is within that frame that I am building these Command Buttons & there will be a variable amount of them, depending upon the number of Rows in a specific Sheet. That is why I am building them on the fly. I have also added via code a scroll bar and this allows me to scroll up & down within the frame to see the (currently) 29 individual Command Buttons.

It's not the easiest thing in the world to explain, but it's not ideal in that it is a Spreadsheet of my nephew's that I am trying to automate for him ... were I starting from scratch I wouldn't be using Excel at all, but that's a different story ...



What is a scrolling list of Commnad Buttons, and where does that code go in this example.

Bob Phillips
03-06-2009, 05:36 AM
Why not post it here, it seems that there is no confidentiality issue.

Bob Phillips
03-06-2009, 05:36 AM
BTW, are you Irish in England, or English in Ireland?

vodkasoda
03-06-2009, 06:03 AM
OK, this is the Spreadsheet ... it should automatically go to the screen I am working on, as I've branched past all of the menus ... hopefully you will see the problem !!!

vodkasoda
03-06-2009, 06:04 AM
I'm English in Ireland ... been here since June 2001 & 2 of my kids were born here ...

Bob Phillips
03-06-2009, 06:35 AM
This works.

To get back to Steve's point, adding buttons on the fly is not the best solution here. It would be far better to put all the player details in a listbox, and trap the listbox click, or even have one action button that actions the selected player.

vodkasoda
03-06-2009, 07:34 AM
Thank-you ... as far as I can see, the only change you have made is to add the command

Dim mcolEvents As Collection

at the start of the Module, whereas I had it as a Public command in the Definitions Module.

Firstly, is that all that you did & secondly, if so, there is obviously something I don't understand (nothing new there !) ... does it not always work having a Public definition in a separate module ?!?

I obviously want to know, as I want to learn how to do these things myself & it ain't easy !!! I do understand what you're saying about creating on the fly, and will definitely take that into consideration as I progress ... thanks again :friends: .


This works.

To get back to Steve's point, adding buttons on the fly is not the best solution here. It would be far better to put all the palyer details in a listbox, and trap the listbox click, or even have one action button that actions the selected player.

Bob Phillips
03-06-2009, 07:48 AM
Thank-you ... as far as I can see, the only change you have made is to add the command

Dim mcolEvents As Collection

at the start of the Module, whereas I had it as a Public command in the Definitions Module.

Ah, I missed that, I wondered where the others were. You can delete that line then.


Firstly, is that all that you did & secondly, if so, there is obviously something I don't understand (nothing new there !) ... does it not always work having a Public definition in a separate module ?!?

I obviously want to know, as I want to learn how to do these things myself & it ain't easy !!! I do understand what you're saying about creating on the fly, and will definitely take that into consideration as I progress ... thanks again :friends: .

No, I also added the line



Set mcolEvents = New Collection

vodkasoda
03-06-2009, 07:58 AM
Got it ... thanks very much for this ... my new favourite forum :dance: !!!!!

Bob Phillips
03-06-2009, 08:28 AM
And so it should be. I am as active as I am here because it is so good.

It is different to other forums. In general, we get few of the types of questions that pervade most other forums, the ones where you reall want to answer RTFM, or Google it. The posters have usually tried things first, and are trying to develop their skills. Questions are generally well constructed, and we can work with the poster to dive deeper, and of course the forum allows attachments.

And the regulars are good guys, few egos, and very little of the competitive answering nature of forums like MrExcel and ExcelForum.

So all in all, it is the best, and lets hope the influx of newcomers maintain these standards.