using Sub's with checkboxs

[dave bryden]

Hi,
I'm fairly new to this type of programming and I was hoping
someone could clarify where I'm going wrong.

What I'm trying to do is this:

on sheet 1 I have put in a checkbox from the control bar. When the box
is ticked, I would like to propogate that tick to 2 other sheets. At
the moment the cells on the other 2 sheets are also checkboxes, mainly
cause I don't have this working.

I've gone thru the help, and have pulled several examples from this
group, however I still don't have it working and I think I have a
basic underlying misunderstanding.

this is what I've done:

on sheet 1, use the control bar to define a checkbox. I then named the
object. So my checkbox might be checkbox217, I've then named it box1.
select the cell and then gone into design mode and selected view code.

this brings up the pane to show that this code belongs to this
worksheet, and in the edit window I have
sub checkbox217_click ()
with box1
If .Value = True Then
ActiveSheet.Range("sheet1!$a$3").Value = 10
Else
ActiveSheet.Range("sheet1!$a$3") = ""
End If
End With
end sub

To get this going, I've simplified this so that if the box is ticked,
I put the value 10 in cell A3

when I run this I get an error saying object required.

Could someone perhaps help me understand what I'm doing wrong here?

1) when I go to the macro screen, I see sub checkbox217_click (), The
checkbox217 was assigned when I defined the checkbox, I named that box
to be box1, should I use box1 in this line?

2) when is the "With box1" required. is that to establish a
relationship between the name I have given the cell and the name that
was given by Excel when I defined it? Is the BOX1 then to be used
within the macro to reference the cell?

3) How do I reference that cell? I've seen examples where they use
just .value = True, I seen checkbox1.value = True, I've seen examples
where Activesheet is used. I don't have a clear understanding of which
format is to be used when.

4) Would it be better to have the cells on the "destination" sheets
defined as text fields rather than as a controlbar checkbox?
On my first sheet I have a list of aircraft, some will be active, some
will be inactive. on the first sheet, I tick the box when an aircraft
becomes active. I want the macro to tick a corresponding box on the
current status page, and also tick the corresponding box on the flight
following page.

Could someone help point me in the correct direction please?

many thanks
Dave

 

[grinning_crow]

I'm not 100% sure, but you may want to try changing:

with box1

to:

with sheet1.box1

Also, consider whether the code you have is on a module or on the shee
or on the workbook, because this will make a difference whe
referencing objects. Also, be doubly sure you have actually changed th
Name of the checkbox to box1 and not the Caption.

I hope this is of some help

 

[Bob Phillips]

1) when I go to the macro screen, I see sub checkbox217_click (), The
checkbox217 was assigned when I defined the checkbox, I named that box
to be box1, should I use box1 in this line?

If you renamed the checkbox Box1 then the event should become

Private Sub Box1_Click()

2) when is the "With box1" required. is that to establish a
relationship between the name I have given the cell and the name that
was given by Excel when I defined it? Is the BOX1 then to be used
within the macro to reference the cell?

No, that just sets a pointer to the object, to stop having to qualify other
commands with the object being acted upon (.Value in this case), reduce the
amount of coding, make it more readable and efficient. But it should refer
to the object name, which should be Box1 in your example.

3) How do I reference that cell? I've seen examples where they use
just .value = True, I seen checkbox1.value = True, I've seen examples
where Activesheet is used. I don't have a clear understanding of which
format is to be used when.

As mentioned above, you can use just .Value because you have set a pointer
to the object with the With clause, so .Value is resolved as Box1.Value at
run time, or more accurately the Value property of the Box1 object

4) Would it be better to have the cells on the "destination" sheets
defined as text fields rather than as a controlbar checkbox?
On my first sheet I have a list of aircraft, some will be active, some
will be inactive. on the first sheet, I tick the box when an aircraft
becomes active. I want the macro to tick a corresponding box on the
current status page, and also tick the corresponding box on the flight
following page.

Not necessarily.

Try this amended code, you don't need a sheet in the range as you reference
via Activesheet.

Private Sub Box1_Click()
With box1
If .Value = True Then
ActiveSheet.Range("A3").Value = 10
Else
ActiveSheet.Range("A3") = ""
End If
End With

End Sub

 

[Dave Bryden]

Many thanks,

Am I correct in thinking that the code would be applied to the worksheet
since this event is specific to the checkbox on this sheet? If the macro
dealt with something that concerned the whole workbook that it would
have workbook focus?

I tried the modified code, while it doesn't generate errors, it also
doesn't put anything in the cell.
I used the control bar to define the checkbox and while it was showing
as refernce in the formula bar, used name>define to add the name BOX1,
in the formula bar I have: =EMBED("Forms.CheckBox.1","")

then in the code I have:
Private Sub Box1_Click()
With Box1
If .Value = True Then
ActiveSheet.Range("A3").Value = 10
Else
ActiveSheet.Range("A3") = ""
End If
End With

End Sub

have I done this correctly?

thanks

 

[Bob Phillips]

Many thanks,

Am I correct in thinking that the code would be applied to the worksheet
since this event is specific to the checkbox on this sheet? If the macro
dealt with something that concerned the whole workbook that it would
have workbook focus?

That's right, although clearly controls have to be on a worksheet, there is
no workbook control.

I tried the modified code, while it doesn't generate errors, it also
doesn't put anything in the cell.

It worked with me, so that suggests that the control is not called Box1.

I used the control bar to define the checkbox and while it was showing
as refernce in the formula bar, used name>define to add the name BOX1,
in the formula bar I have: =EMBED("Forms.CheckBox.1","")

I thin k we have the problem here. You don 't change the name via
Name>Define. The name wikll appear in the Names box, to the left of the
formula bar, and this is is where you should change the name. Slect the
object in design mode and change it n ow and try it again.


If all this still fails to work, mail me the workbook at bob . phillips @
tiscali . co . uk (remove the spaces) and I will fix it.