PDA

View Full Version : Sleeper: How do I get an OLEObjects check box to call a function when clicked? (VBA)



dantheman82
10-28-2004, 07:12 AM
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?

Please note that I can have any number of check boxes.

Thanks!
Dan

Glaswegian
10-28-2004, 07:34 AM
Hi Dan

Could you use the Click_Event of the checkbox to run your code? Something like


If CheckBox1 = True Then
RunNow(Target.Row)
End If


Regards

dantheman82
10-28-2004, 07:37 AM
Hi Dan

Could you use the Click_Event of the checkbox to run your code? Something like


If CheckBox1 = True Then
RunNow(Target.Row)
End If


Regards
Ideally, yes. However, how would I be able to access the value of the Checkbox and at what point should I include the code?

Glaswegian
10-28-2004, 08:10 AM
Dan

I noticed you said you could have quite a few checkboxes. Do you want your code to run if ANY checkbox is checked?

Regards

dantheman82
10-28-2004, 09:33 AM
Yes, that is precisely it. I have X number of checkboxes imbedded in the first column of X rows.

Dan

Glaswegian
10-28-2004, 11:29 AM
Dan



Hopefully this is what you mean. This will loop through all the checkboxes and run your code if any checkbox is checked. In place of the message box line just put in your own macro line.



Sub CheckboxMacro()
Dim ole As OLEObject
For Each ole In ActiveSheet.OLEObjects
If ole.Object.Value = True Then
MsgBox ole.Name
End If
Next ole
End Sub

HTH

Regards


PS Sorry for the delay in replying. The Thought Police seemed to be working overtime at the office - I could not log back in!!

dantheman82
10-28-2004, 11:44 AM
That is incredibly close! However, one problem which is the real trouble for me:
What event can I use to trigger this function? Basically, when the checkbox value changes, I have no way of knowing it...

So far, I've been unable to find an event that will do this. Worksheet_Change only works when cells are manually changed (not when linked cells are automatically changed).

Thanks,
Dan

Ken Puls
10-28-2004, 12:05 PM
Can you use the following event?


Private Sub CheckBox1_Change()
'code to call you routine here
End Sub


Of course you'd want to change the name of your checkbox1 to the name of your actual checkbox, and would need an event for each.

Personally, I'd avoid caling the worksheet_change event though. I prefer to code a separate macro and call it from wherever I need to... whether worksheet_change, checkboxn_change, etc... (makes it easier to update later, and less confusing to anyone else who has to modify your code.)

HTH,

dantheman82
10-28-2004, 03:56 PM
Here's the issue:



Private Sub CheckBox1_Change()
'code to call your routine here
End Sub

I need to create a CheckBoxChange function for X different textboxes which are not named yet. Would I Insert the fuctions programmatically?

Ken Puls
10-28-2004, 04:05 PM
Hmmm...

I believe that this handy bit of code came from Nate Oliver a long time back, but could be wrong. I'll give him credit for it anyway... :yes

This code is an example of how to add code programatically to a module:


'ActiveWorkBook.VBProject.VBComponents.Add(1) 'to add a new Module1
With ActiveWorkBook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Private Sub RollForward_Click()" & vbLf & _
" Msgbox ""Here is the new procedure"" " & vbLf & _
"End Sub"
End With
With ActiveWorkBook.VBProject.VBComponents(Activesheet.CodeName).CodeModule
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Private Sub PrintAll_Standard_Click()" & vbLf & _
" Msgbox ""Here is the new procedure"" " & vbLf & _
"End Sub"
End With

Now, you will need a reference to the Visual Basic for Application Extensibility library, and you will also need to ensure that "Trust Access to Visual Basic Project" (in macro security settings) is enabled.

My thought is that you could modify this to set up the proc name based on the name of the cb you create.

Does that make sense?

Ken Puls
10-29-2004, 09:11 AM
Hi there,

Sorry, had to post in a hurry last night.

Try this and see if it works. I haven't tested it, but think it should work. Remember to set your reference to the Extesibility library and trust access to the VB project (if using Excel XP/2003)

It should create a msgbox for each function in code. Once you verify that it works, you can change the msgbox portion to call your desired proc.


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
With ActiveWorkBook.VBProject.VBComponents(ws.CodeName).CodeModule
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Private Sub " & cb.name & "_Change()" & vbLf & _
" Msgbox ""I just changed values!"" " & vbLf & _
"End Sub"
End With
Next c
Application.ScreenUpdating = True
End Sub

HTH,