Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 23rd, 2007, 09:11 AM
Registered User
Join Date: Aug 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Adding Event Handling Programmatically

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.
Old August 29th, 2007, 10:58 PM
Registered User
Join Date: Aug 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts

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

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:= _
End Sub

you can generalise it.

Old August 30th, 2007, 10:42 AM
Registered User
Join Date: Aug 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

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.

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Event Handling Bill Crawley ASP.NET 2.0 Basics 0 February 28th, 2007 07:32 AM
Adding List Item Programmatically rit01 BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 2 February 12th, 2006 04:51 PM
Programmatically Loaded User Control Event Handlin cgoldrin ASP.NET 1.0 and 1.1 Professional 4 December 2nd, 2003 09:59 PM
Adding datagrid programmatically at a specific pos pankaj_daga ASP.NET 1.0 and 1.1 Basics 4 October 23rd, 2003 03:10 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.