Dynamically Adding Code to Buttons on an existing UserForm
 

Dear All;
I am attempting to add code to buttons on a userform that exists
as part of an excel add-in. I have created the rest of the code, and
it all works OK, however I have changed from a dynamically created
userform, created at design time, to an existing userform with
controls and sizes added at runtime so that I can password protect the
project.

Unfortunately, whereas in the dynamic userform, I could use the
following code:

Set frmPrintAll = ThisWorkbook.VBProject.VBComponents.Add(3)
With frmPrintAll

'... Rest of code to dynamically create size, buttons, etc
....'

With frmPrintAll.CodeModule
x = .CountOfLines
.InsertLines x + 1, "Sub CommandButton1_Click()"
.InsertLines x + 2, " intClicked=-1"
.InsertLines x + 3, " Unload Me"
.InsertLines x + 4, "End Sub"

.InsertLines x + 5, "Sub CommandButton2_Click()"
.InsertLines x + 6, " intClicked=1"
.InsertLines x + 7, " Unload Me"
.InsertLines x + 8, "End Sub"
End With

When I attempt to modify the .CodeModue on the non-dynamically
created userform, nothing happens. I have a dimension statement at the
start of the code, which is accessed in the following way:

dim frmPrintAll as new frmPrintAll

I have tried created static code with the above details in them,
however when you click on the buttons, despite them being called
CommandButton1 and CommandButton2 (As far as I can tell, anyway)
nothing happens.

Does anybody have any solutions to this puzzling problem? Any
response will be much appreciated.

Peter Street
OLE Alliance.

Dynamically Adding Code to Buttons on an existing UserForm
 

If you protect your code, you won't be able to add code anyway. When a
project is locked, it is locked. You can't modify it any vbe aspect of it
with code.

--
Regards,
Tom Ogilvy


Peter Street wrote in message
om...
Dear All;
I am attempting to add code to buttons on a userform that exists
as part of an excel add-in. I have created the rest of the code, and
it all works OK, however I have changed from a dynamically created
userform, created at design time, to an existing userform with
controls and sizes added at runtime so that I can password protect the
project.

Unfortunately, whereas in the dynamic userform, I could use the
following code:

Set frmPrintAll = ThisWorkbook.VBProject.VBComponents.Add(3)
With frmPrintAll

'... Rest of code to dynamically create size, buttons, etc
...'

With frmPrintAll.CodeModule
x = .CountOfLines
.InsertLines x + 1, "Sub CommandButton1_Click()"
.InsertLines x + 2, " intClicked=-1"
.InsertLines x + 3, " Unload Me"
.InsertLines x + 4, "End Sub"

.InsertLines x + 5, "Sub CommandButton2_Click()"
.InsertLines x + 6, " intClicked=1"
.InsertLines x + 7, " Unload Me"
.InsertLines x + 8, "End Sub"
End With

When I attempt to modify the .CodeModue on the non-dynamically
created userform, nothing happens. I have a dimension statement at the
start of the code, which is accessed in the following way:

dim frmPrintAll as new frmPrintAll

I have tried created static code with the above details in them,
however when you click on the buttons, despite them being called
CommandButton1 and CommandButton2 (As far as I can tell, anyway)
nothing happens.

Does anybody have any solutions to this puzzling problem? Any
response will be much appreciated.

Peter Street
OLE Alliance.

Dynamically Adding Code to Buttons on an existing UserForm
 

Thanks very much, Tom. I don't suppose you know of any way around
this? I'm trying to set this up so that it can be used by a number of
people, quite a few have "Technical" expertise - in other words, they
are experienced enough to fiddle with the code, and not experienced
enough to realise that doing such can cause silly things to happen
(Like trying to print out the normal.dot file - Excel didn't really
like that).

Again, thanks for the response.
--
Peter Street
OLE Alliance.

"Tom Ogilvy" wrote in message ...
If you protect your code, you won't be able to add code anyway. When a
project is locked, it is locked. You can't modify it any vbe aspect of it
with code.

--
Regards,
Tom Ogilvy


Peter Street wrote in message
om...
Dear All;
I am attempting to add code to buttons on a userform that exists
as part of an excel add-in. I have created the rest of the code, and
it all works OK, however I have changed from a dynamically created
userform, created at design time, to an existing userform with
controls and sizes added at runtime so that I can password protect the
project.

Unfortunately, whereas in the dynamic userform, I could use the
following code:

Set frmPrintAll = ThisWorkbook.VBProject.VBComponents.Add(3)
With frmPrintAll

'... Rest of code to dynamically create size, buttons, etc
...'

With frmPrintAll.CodeModule
x = .CountOfLines
.InsertLines x + 1, "Sub CommandButton1_Click()"
.InsertLines x + 2, " intClicked=-1"
.InsertLines x + 3, " Unload Me"
.InsertLines x + 4, "End Sub"

.InsertLines x + 5, "Sub CommandButton2_Click()"
.InsertLines x + 6, " intClicked=1"
.InsertLines x + 7, " Unload Me"
.InsertLines x + 8, "End Sub"
End With

When I attempt to modify the .CodeModue on the non-dynamically
created userform, nothing happens. I have a dimension statement at the
start of the code, which is accessed in the following way:

dim frmPrintAll as new frmPrintAll

I have tried created static code with the above details in them,
however when you click on the buttons, despite them being called
CommandButton1 and CommandButton2 (As far as I can tell, anyway)
nothing happens.

Does anybody have any solutions to this puzzling problem? Any
response will be much appreciated.

Peter Street
OLE Alliance.