Dynamically assign macro

[John Smith]

Hi,
I am creating a column of checkboxes that corresponds directly to a
list of employee names. The last checkbox is to select all the names.
Because the list of names will always vary in length I need to know
how to assign the macro to the last checkbox. Any ideas? Thanks.
James

Sub AddCBX() 'for OptBtn2
Dim WB As Workbook
Dim sh As Worksheet
Dim rng As Range
Dim rCell As Range
Dim LastRow As Long


Set WB = ThisWorkbook
Set sh = WB.Sheets("Sheet2")
sh.Select
sh.CheckBoxes.Delete
sh.Columns(1).Insert
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Set rng = sh.Range("A1", "A" & LastRow + 1)

Application.ScreenUpdating = False
For Each rCell In rng.Cells
With sh.CheckBoxes.Add(rCell.Left + 5, rCell.Top - 2, 5, 5)
.Caption = ""
.LinkedCell = rCell.Address(False, False)
End With
rCell.Font.Color = vbWhite
Next rCell
sh.Columns(1).ColumnWidth = 3.86
With sh.Cells(LastRow + 1, "B")
.Value = "Select All"
.Select
With Selection.Font
.ColorIndex = xlAutomatic
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
End With
End With
Application.ScreenUpdating = True
End Sub

Sub AllCheck() 'to select all names
Dim myCBX As CheckBox
For Each myCBX In ActiveSheet.CheckBoxes
myCBX.Value = xlOn
Next myCBX
End Sub

 

[Cimjet]

Hi John
Your code works fine for Checkbox from the Forms menu not the Control toolbox.
Try this if it works for you.
Sub Checkall()
Dim shp As OLEObject
For Each shp In ActiveSheet.OLEObjects
If shp.progID = "Forms.CheckBox.1" Then
shp.Object.Value = True
End If
Next shp
End Sub
Cimjet

 

[John Smith]

Hi John
Your code works fine for Checkbox from the Forms menu not the Control toolbox.
Try this if it works for you.
Sub Checkall()
Dim shp As OLEObject
For Each shp In ActiveSheet.OLEObjects
   If shp.progID = "Forms.CheckBox.1" Then
      shp.Object.Value = True
   End If
Next shp
End Sub









- Show quoted text -

Thanks, but I guess I don't understand what you're trying to tell me.
You mean I need to change the type of checkboxes on the worksheet?

 

[Cimjet]

John
Did you try the script I sent you ?
And what type of Checkbox are you using?
Cimjet

Hi John
Your code works fine for Checkbox from the Forms menu not the Control toolbox.
Try this if it works for you.
Sub Checkall()
Dim shp As OLEObject
For Each shp In ActiveSheet.OLEObjects
If shp.progID = "Forms.CheckBox.1" Then
shp.Object.Value = True
End If
Next shp
End Sub









- Show quoted text -

Thanks, but I guess I don't understand what you're trying to tell me.
You mean I need to change the type of checkboxes on the worksheet?

 

[John Smith]

John
Did you try the script I sent you ?
And what type of Checkbox are you using?




Thanks, but I guess I don't understand what you're trying to tell me.
You mean I need to change the type of checkboxes on the worksheet?- Hide quoted text -

- Show quoted text -

Yes, I tried it, but I'm not using Active X checkboxes, so at "For
Each shp In ActiveSheet.OLEObjects" it went to the end of the sub. I
have decided to move the 'Select All' checkbox to A1, so that it will
always be in the same place. Does that make it any easier?

 

[Cimjet]

If you're not using ActiveX Checkbox, I don't know why your macro is'nt
working.It work fine for me.
I set up a few checkbox and ran your code. Works ok

John
Did you try the script I sent you ?
And what type of Checkbox are you using?




Thanks, but I guess I don't understand what you're trying to tell me.
You mean I need to change the type of checkboxes on the worksheet?- Hide
quoted text -

- Show quoted text -

Yes, I tried it, but I'm not using Active X checkboxes, so at "For
Each shp In ActiveSheet.OLEObjects" it went to the end of the sub. I
have decided to move the 'Select All' checkbox to A1, so that it will
always be in the same place. Does that make it any easier?

 

[John Smith]

If you're not using ActiveX Checkbox, I don't know why your macro is'nt
working.It work fine for me.






Yes, I tried it, but I'm not using Active X checkboxes, so at "For
Each shp In ActiveSheet.OLEObjects" it went to the end of the sub. I
have decided to move the 'Select All' checkbox to A1, so that it will
always be in the same place. Does that make it any easier?- Hide quoted text -

- Show quoted text -

I'm not having a problem with the code, I'm having a problem
programmitically assigning the sub to the 'Select All' checkbox.

 

[Cimjet]

Nowww I understand, sorry about that.
I will play with it, never did that. but it would be much easier to put a button
or a graphic

If you're not using ActiveX Checkbox, I don't know why your macro is'nt
working.It work fine for me.
I set up a few checkbox and ran your code. Works ok"John Smith"






Yes, I tried it, but I'm not using Active X checkboxes, so at "For
Each shp In ActiveSheet.OLEObjects" it went to the end of the sub. I
have decided to move the 'Select All' checkbox to A1, so that it will
always be in the same place. Does that make it any easier?- Hide quoted text -

- Show quoted text -

I'm not having a problem with the code, I'm having a problem
programmitically assigning the sub to the 'Select All' checkbox.

 

[Cimjet]

Do you have a cell with a volatile formula like Date or now or anyone that would
trigger recalculation.
I'm using a cell link (A1)for the checkbox and I added one line to your macro.
But it will only work if you have a volatile formula on your sheet
Private Sub Worksheet_Calculate()
Dim myCBX As CheckBox
If Cells(1, 1) = True Then
For Each myCBX In ActiveSheet.CheckBoxes
myCBX.Value = xlOn
Next myCBX
End If
End Sub
Would that help?

If you're not using ActiveX Checkbox, I don't know why your macro is'nt
working.It work fine for me.
I set up a few checkbox and ran your code. Works ok"John Smith"






Yes, I tried it, but I'm not using Active X checkboxes, so at "For
Each shp In ActiveSheet.OLEObjects" it went to the end of the sub. I
have decided to move the 'Select All' checkbox to A1, so that it will
always be in the same place. Does that make it any easier?- Hide quoted text -

- Show quoted text -

I'm not having a problem with the code, I'm having a problem
programmitically assigning the sub to the 'Select All' checkbox.

 

[John Smith]

Do you have a cell with a volatile formula like Date or now or anyone that would
trigger recalculation.
I'm using a cell link (A1)for the checkbox and I added one line to your macro.
But it will only work if you have a volatile formula on your sheet
Private Sub Worksheet_Calculate()
Dim myCBX As CheckBox
If Cells(1, 1) = True Then
    For Each myCBX In ActiveSheet.CheckBoxes
        myCBX.Value = xlOn
     Next myCBX
    End If
End Sub
Would that help?







I'm not having a problem with the code, I'm having a problem
programmitically assigning the sub to the 'Select All' checkbox.- Hide quoted text -

- Show quoted text -

That worked great! I'm still not sure how to link it to that first
checkbox. Will the first checkbox always be named the same numerically?

 

[Cimjet]

You right click the checkbox and select Format control.
Where you see "Cell link" type a cell reference, I chose A1 but can be any cell,
make sure you select the same cell in the macro.
HTH
Cimjet

Do you have a cell with a volatile formula like Date or now or anyone that
would
trigger recalculation.
I'm using a cell link (A1)for the checkbox and I added one line to your macro.
But it will only work if you have a volatile formula on your sheet
Private Sub Worksheet_Calculate()
Dim myCBX As CheckBox
If Cells(1, 1) = True Then
For Each myCBX In ActiveSheet.CheckBoxes
myCBX.Value = xlOn
Next myCBX
End If
End Sub
Would that help?







I'm not having a problem with the code, I'm having a problem
programmitically assigning the sub to the 'Select All' checkbox.- Hide quoted
text -

- Show quoted text -

That worked great! I'm still not sure how to link it to that first
checkbox. Will the first checkbox always be named the same numerically?

 

[John Smith]

You right click the checkbox and select Format control.
Where you see "Cell link" type a cell reference, I chose A1 but can be any cell,
make sure you select the same cell in the macro.
HTH







That worked great! I'm still not sure how to link it to that first
checkbox. Will the first checkbox always be named the same numerically?- Hide quoted text -

- Show quoted text -

That's my problem - how do I get that in a macro? I've tried recording
a macro, but the checkbox value keeps changing on me. Is there another
way to reference that very first checkbox?

 

[Cimjet]

I forgot to say ..
We are not using the name of the checkbox, it's the cell reference that we are
using.

Do you have a cell with a volatile formula like Date or now or anyone that
would
trigger recalculation.
I'm using a cell link (A1)for the checkbox and I added one line to your macro.
But it will only work if you have a volatile formula on your sheet
Private Sub Worksheet_Calculate()
Dim myCBX As CheckBox
If Cells(1, 1) = True Then
For Each myCBX In ActiveSheet.CheckBoxes
myCBX.Value = xlOn
Next myCBX
End If
End Sub
Would that help?







I'm not having a problem with the code, I'm having a problem
programmitically assigning the sub to the 'Select All' checkbox.- Hide quoted
text -

- Show quoted text -

That worked great! I'm still not sure how to link it to that first
checkbox. Will the first checkbox always be named the same numerically?

 

[Cimjet]

I'm lost, you're creating a checkbox everytime you run the macro!!!

You right click the checkbox and select Format control.
Where you see "Cell link" type a cell reference, I chose A1 but can be any
cell,
make sure you select the same cell in the macro.
HTH







That worked great! I'm still not sure how to link it to that first
checkbox. Will the first checkbox always be named the same numerically?- Hide
quoted text -

- Show quoted text -

That's my problem - how do I get that in a macro? I've tried recording
a macro, but the checkbox value keeps changing on me. Is there another
way to reference that very first checkbox?

 

[John Smith]

I'm lost, you're creating a checkbox everytime you run the macro!!!







That's my problem - how do I get that in a macro? I've tried recording
a macro, but the checkbox value keeps changing on me. Is there another
way to reference that very first checkbox?- Hide quoted text -

- Show quoted text -

Yes, because I never know how many rows I will end up with from time
to time. I guess I could keep the 'Select All' checkbox on the form
and just fill below it. Would that cause any new coding problems?

 

[Cimjet]

No problem, that's what you should do.
The macro will select them all no matter if you keep adding some.Just keep the
same checkbox for that function.
Cimjet

I'm lost, you're creating a checkbox everytime you run the macro!!!







That's my problem - how do I get that in a macro? I've tried recording
a macro, but the checkbox value keeps changing on me. Is there another
way to reference that very first checkbox?- Hide quoted text -

- Show quoted text -

Yes, because I never know how many rows I will end up with from time
to time. I guess I could keep the 'Select All' checkbox on the form
and just fill below it. Would that cause any new coding problems?

 

[John Smith]

No problem, that's what you should do.
The macro will select them all no matter if you keep adding some.Just keep the
same checkbox for that function.







Yes, because I never know how many rows I will end up with from time
to time. I guess I could keep the 'Select All' checkbox on the form
and just fill below it. Would that cause any new coding problems?- Hide quoted text -

- Show quoted text -

Thanks Cimjet, I really appreciate all of your help and patience!
James

 

[Cimjet]

You're welcome
Glad to help
Cimjet

No problem, that's what you should do.
The macro will select them all no matter if you keep adding some.Just keep the
same checkbox for that function.







Yes, because I never know how many rows I will end up with from time
to time. I guess I could keep the 'Select All' checkbox on the form
and just fill below it. Would that cause any new coding problems?- Hide quoted
text -

- Show quoted text -

Thanks Cimjet, I really appreciate all of your help and patience!
James

 

[John Smith]

No problem, that's what you should do.
The macro will select them all no matter if you keep adding some.Just keep the
same checkbox for that function.







Yes, because I never know how many rows I will end up with from time
to time. I guess I could keep the 'Select All' checkbox on the form
and just fill below it. Would that cause any new coding problems?- Hide quoted text -

- Show quoted text -

How would I protect that single cell, since I need to delete
everything else on the page every time it gets used?

 

[Cimjet]

Hi again
Select that checkbox and in the name box above cell A1 give it a name.I called
it "stock" in the macro. You need to change it to your choice.
This way it will be unique. then this will delete all but that one.

Sub saveonebox()
Dim myCBX As CheckBox
For Each myCBX In ActiveSheet.CheckBoxes
If Not myCBX.Name = "stock" Then
myCBX.Delete
End If
Next myCBX
End Sub
Cimjet

No problem, that's what you should do.
The macro will select them all no matter if you keep adding some.Just keep the
same checkbox for that function.







Yes, because I never know how many rows I will end up with from time
to time. I guess I could keep the 'Select All' checkbox on the form
and just fill below it. Would that cause any new coding problems?- Hide quoted
text -

- Show quoted text -

How would I protect that single cell, since I need to delete
everything else on the page every time it gets used?