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

fitzrandolph

New Member
Joined
Sep 5, 2002
Messages
24
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

New Member
Joined
Sep 5, 2002
Messages
24

ADVERTISEMENT

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

New Member
Joined
Sep 5, 2002
Messages
24
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959

ADVERTISEMENT

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

New Member
Joined
Sep 5, 2002
Messages
24
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

New Member
Joined
Sep 5, 2002
Messages
24
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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 !
 

Watch MrExcel Video

Forum statistics

Threads
1,130,047
Messages
5,639,763
Members
417,109
Latest member
996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top