Using a checkbox to trigger action

[Thompa]

Hi,
New to this board and to using Visual Basic in Excel - so please bear with me!

All that I want to do is to be able to use a check box to switch a row of cells to bold and by unchecking the box switch off the bold effect.

I can assign a macro to create the bold effect but don't have enough knowledge to extend the macro to toggle the effect off again!

I tried to use a radio button - and could turn it on - couldn't check it off!

This must be an easy one for you guys!

Looking forwqard to hearing from you.

regards,
Allan Thompson

 

[Smitty]

Welcome to the Board Allan!

How about something like this to toggle Bold:

Private Sub CheckBox1_Click()
    ActiveCell.EntireRow.Font.Bold = Not ActiveCell.EntireRow.Font.Bold
End Sub

Hope that helps,

Smitty

 

[Glaswegian]

Hi Allan and Welcome to the Board!

Draw a checkbox using the Control Toolbox. While still in Edit mode, right click over the checkbox and choose 'View Code'. The VB Editor will now appear. Paste in the following code between the 'Private Sub' and 'End Sub' lines.

If CheckBox1 = True Then
Rows(3).Font.Bold = True
Else
Rows(3).Font.Bold = False
End If
End Sub

This will make all data in row C bold when checked and vice versa. Change the row number to suit. Does this help?

Regards

Smitty - TDQ !!!

 

[Thompa]

Nearly works

Hi Smitty,

Thanks for the quick reply.
I found that your routine wouldn't work and so adapted it to:-

If CheckBox9 = True Then
Range("B5:Z5").Select
Selection.Font.Bold = True
Else
Range("B5:Z5").Select
Selection.Font.Bold = False
End If
End Sub

Now that sort of works - but is not consistent.
If the range is bold already then checking the box, un-'bolds' it.... but checking the box doesn't do anything but select the range... no Bold

I would like an unchecked box to produce plain text and a checked box produce bold text in hte range.

By way of explanation as to what I am trying to do; I have a worksheet of 120 rows and want to have Column A with checkboxes to highlight each row or not - if not checked. I was hoping then to be able to print out just the checked/bold rows... is that possible?

Of course there may be a better way of doing this... I am experimenting! I seem to already have a problem with overlapping check box areas...

thanks again.

Allan

 

[Smitty]

You can add Bold test to Ian's code:

Private Sub CheckBox1_Click()
    If CheckBox1 = True Then
        If Range("A5:Z5").Font.Bold = True Then
            Exit Sub
        Else: Range("B5:Z5").Font.Bold = True
        End If
    Else
        Range("B5:Z5").Font.Bold = False
    End If
End Sub

I have no idea why you couldn't get my code to run though.

Did you get an error message?

Smitty

 

[Thompa]

Checkboxes

Hi again,

SAme thing with your new routine.. if the range is already Bold then checking the box un-'bolds' it otherwise no change.

There are no error messages - just no action.

I am using Excel 2002... could that be a problem?
In the Visual Basic Editor, I have:-
Sub row5bold()
'Macro to make Row 5 bold or not on clicking check box

If CheckBox9 = True Then
If Range("B5:Z5").Font.Bold = True Then
Exit Sub
Else: Range("B5:Z5").Font.Bold = True
End If
Else
Range("B5:Z5").Font.Bold = False
End If
End Sub

Allan