Inserting Event into the Excel WorkSheet from VB

D

dgchoudary

New Member
Joined
Jul 1, 2002
Messages
6
Hi

I am loading a Excel application with Data from the Visual Basic at the same time i need the following event also to be loaded into the active worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngActiveRange As Excel.Range

' Range object returned from the Selection property.
Set rngActiveRange = Selection
Call PrintRangeInfo(rngActiveRange)
End Sub

so that when i click on the each cell this event wiil be fired. how do i load the event into the work sheet from the VB

Thank You

Regrds
Gandhi
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Wow, sounds like a class assignment. Why am I NOT surprised?

Advanced concept (custom event-handling), and not a clue where to put it.

How do you know about Events, without knowing where the code resides?

Noodle around in VBA editor, View Code on the Excel objects, and play around with the drop-down menus in the code window.

Set the left menu for the object in question, I.E. Worksheet, ThisWorkbook.

Notice how the rightmost menu has names for the various events associated with it?

Okay, enough hints.
 
Upvote 0
Hi

I have this code laying about that should help

Code: 
Sub AddSheetWithEventCode()
 'For this procedure to work, you must add a reference to
 'Microsoft Visual Basic for Applications Extensibility
 
 'Declare variables
 Dim ws As Worksheet
 Dim cm As VBIDE.CodeModule
 Dim x As Long
 
 'Create a new worksheet in the active workbook
 Set ws = ActiveWorkbook.Worksheets.Add
 
 'Set a reference to the code module of the new sheet
 Set cm = ws.Parent.VBProject.VBComponents(ws.Name).CodeModule
 
 'Creat a new event procedure
 x = cm.CreateEventProc("Activate", "Worksheet")
 
 'Write code into the procedure
 cm.InsertLines x + 1, "MsgBox ""Hi there!"""
 cm.InsertLines x + 2, "MsgBox ""Hi again!"""
 cm.InsertLines x + 3, "MsgBox ""Hi three!"""
 
 'Release variables
 Set cm = Nothing
 Set ws = Nothing
End Sub

Hope it helps
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Change event problem
2
Replies
13
Views
826
julhs
J
A
  • Question
Worksheet Change Event Triggering With Cell Validation "Cancel"
Replies
1
Views
239
rlv01
R
S
  • Question
Combining multiple worksheet change events into one?
Replies
2
Views
340
sidel801
S
G
  • Question
Macro Code Help for multiple worksheets.
Replies
1
Views
192
Georgiboy
Georgiboy
J
  • Question
Inserting picture into cell via VBA
Replies
2
Views
438
Joek88
J

Forum statistics

Threads
1,215,338
Messages
6,124,358
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top