Adding Event Handling Programmatically

Wallhood · August 23rd, 2007, 09:11 AM

I'm working on an Excel program which requires me to add buttons and worksheets with even handling programmatically. They can not be built beforehand. Everywhere I search describes how to add event handling before runtime which is not what I need. If you could point me in the right direction or give an example I would be most appreciative.

venkat1926 · August 29th, 2007, 10:58 PM

my suggestison is oppen a sheet
creat a macro name the macro
and start adding one or two buttons and and end creating amcro.
now go to that macro and edit you can the lanugage of oepning buttons
now as an event code it depnds upon waht event you are thinking
suppose target is A1. if there is a change in A1 the button should be parked in the sheet a trivial example

[code]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=447, Top:=51.75, Width:=165, Height:= _
76.5).Select
End Sub
[/cod]

you can generalise it.

Wallhood · August 30th, 2007, 10:42 AM

Here is what I ended up doing and it works pretty well. I created a sheet, added the necessary code and then saved that code in another module in the project. When I create the new sheet I copy all the code from the module into the worksheets code module. The code is below.

Code:

Sub InsertCodeIntoWorksheet(ByRef DesignSheet As Worksheet)
Dim StringLine As String
Dim LineCount As Integer

LineCount = ThisWorkbook.VBProject.VBComponents("NewDesignSheetCode").CodeModule.CountOfLines

StringLine = ThisWorkbook.VBProject.VBComponents("NewDesignSheetCode").CodeModule.Lines(1, LineCount)
ThisWorkbook.VBProject.VBComponents(DesignSheet.CodeName).CodeModule.InsertLines 1, StringLine
End Sub

LineCount is the number of lines of code in the module holding the code for the new worksheet. "NewDesignSheetCode" is the name of the module holding the code. StringLine is a string that holds all the lines of code. It ends up being a really easy solution but it took me forever to find. I hope this ends up being useful for some other poor person who needs to be able to do this.