LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default How do I get an OLEObjects check box to call a function when click



I have the following code:

Sub addCheckBox(numrows As Integer)
....
' c is defined as a range in the active sheet in:
' Range("B11:B" & (10 + numrows))
....
For Each c In ws.Range("B11:B" & (10 + numrows))
counter = counter + 1
' This sets a cell which will define the location of the check box
Set cellUnder = c.Offset(0, -1)
' The next line adds the control and sizes and positions
' the control over a cell in the DailyTasks named range.
Set cb = ws.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Left:=width, _
Top:=cellUnder.Top, _
width:=width, _
Height:=cellUnder.Height)

cb.name = (counter + 10)
cb.LinkedCell = "A" & (counter + 10)
cb.PrintObject = False
...
Next c
Application.ScreenUpdating = True
End Sub

As you can see, each check box was created in a loop and linked to the cell
behind it.
I have another function that I would ideally like to run each time the check
box is clicked:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If (Target.Column = 1) Then
If (Range("A" & (Target.row)).Value = True) Then
RunNow (Target.row)
End If
End If
End Sub

However, when I add the OLEObjects check boxes, when I click it, the cell is
changed to True to reflect my action, but Worksheet_Change never is called.
Only by manually typing in "True" in the cell will Worksheet_Change (and aso
RunNow) run.

How can I either:
(a) use my exisiting code and get my check boxes to call the
Worksheet_Change function or
(b) create a macro that every check box will link to which will call the
function on the specific row in which the check box is embedded?

Thanks!
Dan
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Increase size of a Forms Check Box (click on to enter check mark) 718Satoshi Excel Discussion (Misc queries) 0 August 17th 07 01:52 AM
call a function on control click event tkraju via OfficeKB.com Excel Discussion (Misc queries) 7 August 25th 06 07:22 AM
check box, so when you click on it it inserts a check mark into t. Steve Excel Discussion (Misc queries) 2 April 13th 05 09:12 PM
how can i call a sub() automatically when someone click on sheet1 Saadi Excel Worksheet Functions 2 January 7th 05 08:37 PM
Assigning click event to OleObjects checkbox Jim McLeod Excel Programming 5 April 20th 04 07:02 PM


All times are GMT +1. The time now is 09:28 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"