Clicking a CheckBox to change Font..Bold but not using Activ

[fitzrandolph]

Howdy.

This is a wonderful website here. I was wondering if somebody could assist me in this quandry I am in. Please forgive me, I am pretty new to this.

I have this CheckBox (actually have many). I would like to Bold the text in the cell that the CheckBox lives in. The only way I have been able to do it is through ActiveCell. This is a major pain in the butt since I must keep selecting new cells, then click on their CheckBox. Is there a way I can simply check the box without first selecting the cell?

Is my problem that my CheckBox is not "in" the cell and that is why I need to first select the cell?

Here is what I have come up with that works accept for the really annoying part of first clicking on the cell -

Private Sub CheckBox16_Click()
If CheckBox16.Value Then
ActiveCell.Font.Bold = True
ActiveCell.Font.ColorIndex = 3

Else
ActiveCell.Font.Bold = False
ActiveCell.Font.ColorIndex = 1
End If
End Sub

Many thanks!

-- Fitz

 

[Juan Pablo González]

Is this what you need ?

<pre>Private Sub CheckBox1_Click()
With CheckBox1.TopLeftCell
.Font.Bold = CheckBox1.Value
.Font.ColorIndex = IIf(CheckBox1.Value, 3, 1)
End With
End Sub</pre>

 

[Ivan F Moala]

On 2002-09-06 12:50, fitzrandolph wrote:
Howdy.

This is a wonderful website here. I was wondering if somebody could assist me in this quandry I am in. Please forgive me, I am pretty new to this.

I have this CheckBox (actually have many). I would like to Bold the text in the cell that the CheckBox lives in. The only way I have been able to do it is through ActiveCell. This is a major pain in the butt since I must keep selecting new cells, then click on their CheckBox. Is there a way I can simply check the box without first selecting the cell?

Is my problem that my CheckBox is not "in" the cell and that is why I need to first select the cell?

Here is what I have come up with that works accept for the really annoying part of first clicking on the cell -

Private Sub CheckBox16_Click()
If CheckBox16.Value Then
ActiveCell.Font.Bold = True
ActiveCell.Font.ColorIndex = 3

Else
ActiveCell.Font.Bold = False
ActiveCell.Font.ColorIndex = 1
End If
End Sub

Many thanks!

-- Fitz

Hi Fitz and Welcome to the Board
One way to do this given your example;

<pre/>
Private Sub CheckBox1_Click()
'// Just change the address of the cell
With Range("A1").Font
.Bold = CheckBox1.Value
.ColorIndex = IIf(CheckBox1.Value, 3, 1)
End With

End Sub
</pre>

 

[Damon Ostrander]

Hi Fitz,

Are you saying that you don't know which cell the checkbox is "in"? Since you presumably put it there manually I'm wondering how it is that you don't know. Is is because the user of your program might move it around?

If you DO know where the cell is (say, cell B4) then your code would just be:

Private Sub CheckBox16_Click()
If CheckBox16.Value Then
[B4].Font.Bold = True
[B4].Font.ColorIndex = 3
Else
[B4].Font.Bold = False
[B4].Font.ColorIndex = 1
End If
End Sub

If you really don't know where it is then you could do a test on which cell "contained", for example, the upper left corner of the checkbox control. If you want this code, let me know.

 

[fitzrandolph]

Thank you very much Juan and Ivan for the responces.

I have tried out your recommendations and failed. I am certain of user error (on my part of course) and will demonstrate my problem.

<table width=420 border=1>
<tr>
<td>cell</td><td>cell</td>
</tr>
<tr>
<td>Yes [empty checkbox]</td><td>No [empty checkbox]</td>
</tr>
<tr>
<td>Yes [empty checkbox]</td><td><font color=red>No [checked checkbox]</td>
</tr>
<tr>
<td>cell</td><td>cell</td>
</tr>
</table>

I can get it to do the above scenario however, the way I have it set up, I must first click on the cell, then click the check box. I am looking for a way to just click on the checkbox without clicking on the cell for the text to change.

Is that crazy talk?

-- Fitz

 

[fitzrandolph]

Hi Damon.

Thank you for you reply. That is it! However, a new can of worms has opened. Since I have many instances of this, would I need to hard code every cell that is effected?

-- Fitz

 

[Juan Pablo González]

Fitz, that's why I'm using the TopLeftCell, there's another one, called BottomRightCell, you could experiment with either one to see if any of those suit your needs.

 

[fitzrandolph]

On 2002-09-06 13:41, Juan Pablo G. wrote:
Fitz, that's why I'm using the TopLeftCell, there's another one, called BottomRightCell, you could experiment with either one to see if any of those suit your needs.

Hi Juan.
Actually, it was my fault, your code works very well. I had a typo ... Doh!

You people are great.

-- Fitz

 

[fitzrandolph]

Again, Many thanks to you guys helping me.

Now that I have it working, do I need a Private Sub for every CheckBox that will be changed? I attempted to have them all CheckBoxNo instead of CheckBox# but Excel does not like that.

Here is the code so far -

Sub CheckBox_Click(cb)
With cb.TopLeftCell
.Font.Bold = cb.Value
.Font.ColorIndex = IIf(cb.Value, 3, 1)
End With
End Sub

-------------------------------------------
Private Sub CheckBox15_Click()
CheckBox_Click CheckBox15
End Sub

-- Fitz

 

[Juan Pablo González]

Unfortunately, VBA, unlike VB, doesn't like control arrays. There's sort of a solution, that is, to create a class module, and then, using a Collection or an Array, "store" all your checkboxes in there. So, let's begin.

Add a class module, and name it 'clsCB'

Add this code to it:
<pre>Option Explicit

Public WithEvents MyCB As MSForms.CheckBox
Public TpLftCell As Range

Private Sub MyCB_Change()
With TpLftCell
.Font.Bold = MyCB.Value
.Font.ColorIndex = IIf(MyCB.Value, 3, 1)
End With
End Sub</pre>

I added to variables in there, the CheckBox itself (MyCB), and another "property" for the class module, TpLftCell (Wich is TopLeftCell). I include this like this because the MyCB variable doesn't expose all properties that really exist in there, so, we must store them somehow !

I inserted 14 checkboxes on Sheet1, all named CheckBox1, CheckBox2, etc. So, in Sheet1's module, add this code

<pre>Option Explicit

Dim MyCol As Collection

Private Sub Worksheet_Activate()
Dim i As Integer
Dim MyclsCB As clsCB
If MyCol Is Nothing Then
Set MyCol = New Collection
For i = 1 To 14
With Me.OLEObjects("CheckBox" & i)
Set MyclsCB = New clsCB
Set MyclsCB.MyCB = .Object
Set MyclsCB.TpLftCell = .TopLeftCell
MyCol.Add MyclsCB
End With
Next i
End If
End Sub</pre>
I use a collection to store all the 14 checkboxes, I also do the check to see if MyCol has been assigned already, so we don't slow unnecesarily the sheet. Next, I create a new instance of the class module (MyclsCB), and assign to its two properties, MyCB and TpLftCell, the corresponding objects. Finally, I add this "new" instance of the class module to the collection, so we store them in there... and that's it ! just make sure that you activate the sheet (For example, select Sheet2, and then select Sheet1 again) to load the collection, and the Checkboxes should work ok.

You could do that on Workbook_Open, or whenever you set it to.

Good luck !