Hello to everybody,
I have a sheet for showing a certificate of marks. Sometimes Ithe sum of the
marks need to be shown when printing the certificate, and sometime I n don't
need to see the sum.
I have a checkBox named chekcbox1 on the sheet. What I need is a VBA code so
that when the box is checked to hide the contents of the range C34:E34 which
containes the sum of the marks.
PS. by "hiding" I mean "not to be seen. (maybe changing their color to
white!!)
Can anyone help in writing the code for me?

hi
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
Else
Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
End If
End Sub

regards
FSt1

"Khalil Handal" wrote:

> Hello to everybody,
> I have a sheet for showing a certificate of marks. Sometimes Ithe sum of the
> marks need to be shown when printing the certificate, and sometime I n don't
> need to see the sum.
> I have a checkBox named chekcbox1 on the sheet. What I need is a VBA code so
> that when the box is checked to hide the contents of the range C34:E34 which
> containes the sum of the marks.
> PS. by "hiding" I mean "not to be seen. (maybe changing their color to
> white!!)
> Can anyone help in writing the code for me?
>
>
>

Thanks.It worked well

"FSt1" <(E-Mail Removed)> wrote in message
news:21622319-45AD-4D69-B411-(E-Mail Removed)...
> hi
> Private Sub CheckBox1_Click()
> If CheckBox1.Value = True Then
> Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
> Else
> Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
> End If
> End Sub
>
> regards
> FSt1
>
> "Khalil Handal" wrote:
>
>> Hello to everybody,
>> I have a sheet for showing a certificate of marks. Sometimes Ithe sum of
>> the
>> marks need to be shown when printing the certificate, and sometime I n
>> don't
>> need to see the sum.
>> I have a checkBox named chekcbox1 on the sheet. What I need is a VBA code
>> so
>> that when the box is checked to hide the contents of the range C34:E34
>> which
>> containes the sum of the marks.
>> PS. by "hiding" I mean "not to be seen. (maybe changing their color to
>> white!!)
>> Can anyone help in writing the code for me?
>>
>>
>>

Hi,
My sheet is protected and the cells are protected.
It does not work in this case what should I do????


"FSt1" <(E-Mail Removed)> wrote in message
news:21622319-45AD-4D69-B411-(E-Mail Removed)...
> hi
> Private Sub CheckBox1_Click()
> If CheckBox1.Value = True Then
> Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
> Else
> Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
> End If
> End Sub
>
> regards
> FSt1
>
> "Khalil Handal" wrote:
>
>> Hello to everybody,
>> I have a sheet for showing a certificate of marks. Sometimes Ithe sum of
>> the
>> marks need to be shown when printing the certificate, and sometime I n
>> don't
>> need to see the sum.
>> I have a checkBox named chekcbox1 on the sheet. What I need is a VBA code
>> so
>> that when the box is checked to hide the contents of the range C34:E34
>> which
>> containes the sum of the marks.
>> PS. by "hiding" I mean "not to be seen. (maybe changing their color to
>> white!!)
>> Can anyone help in writing the code for me?
>>
>>
>>

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
ActiveSheet.UnProtect
Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
Else
Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
End If
ActiveSheet.Protect
End Sub


Gord Dibben MS Excel MVP

On Tue, 19 Jun 2007 16:52:48 +0300, "Khalil Handal" <(E-Mail Removed)> wrote:

>Hi,
>My sheet is protected and the cells are protected.
>It does not work in this case what should I do????
>
>
>"FSt1" <(E-Mail Removed)> wrote in message
>news:21622319-45AD-4D69-B411-(E-Mail Removed)...
>> hi
>> Private Sub CheckBox1_Click()
>> If CheckBox1.Value = True Then
>> Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
>> Else
>> Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
>> End If
>> End Sub
>>
>> regards
>> FSt1
>>
>> "Khalil Handal" wrote:
>>
>>> Hello to everybody,
>>> I have a sheet for showing a certificate of marks. Sometimes Ithe sum of
>>> the
>>> marks need to be shown when printing the certificate, and sometime I n
>>> don't
>>> need to see the sum.
>>> I have a checkBox named chekcbox1 on the sheet. What I need is a VBA code
>>> so
>>> that when the box is checked to hide the contents of the range C34:E34
>>> which
>>> containes the sum of the marks.
>>> PS. by "hiding" I mean "not to be seen. (maybe changing their color to
>>> white!!)
>>> Can anyone help in writing the code for me?
>>>
>>>
>>>
>

Hi,
When I clicked the box the first time it worked fine.
Then I clicked it again (a second time) and had the following error message:
Unable to set the color property of the font class
any ideas!!!!


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news(E-Mail Removed)...
> Private Sub CheckBox1_Click()
> If CheckBox1.Value = True Then
> ActiveSheet.UnProtect
> Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
> Else
> Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
> End If
> ActiveSheet.Protect
> End Sub
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 19 Jun 2007 16:52:48 +0300, "Khalil Handal" <(E-Mail Removed)>
> wrote:
>
>>Hi,
>>My sheet is protected and the cells are protected.
>>It does not work in this case what should I do????
>>
>>
>>"FSt1" <(E-Mail Removed)> wrote in message
>>news:21622319-45AD-4D69-B411-(E-Mail Removed)...
>>> hi
>>> Private Sub CheckBox1_Click()
>>> If CheckBox1.Value = True Then
>>> Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
>>> Else
>>> Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
>>> End If
>>> End Sub
>>>
>>> regards
>>> FSt1
>>>
>>> "Khalil Handal" wrote:
>>>
>>>> Hello to everybody,
>>>> I have a sheet for showing a certificate of marks. Sometimes Ithe sum
>>>> of
>>>> the
>>>> marks need to be shown when printing the certificate, and sometime I n
>>>> don't
>>>> need to see the sum.
>>>> I have a checkBox named chekcbox1 on the sheet. What I need is a VBA
>>>> code
>>>> so
>>>> that when the box is checked to hide the contents of the range C34:E34
>>>> which
>>>> containes the sum of the marks.
>>>> PS. by "hiding" I mean "not to be seen. (maybe changing their color to
>>>> white!!)
>>>> Can anyone help in writing the code for me?
>>>>
>>>>
>>>>
>>
>

Even more descriptive (in my opinion) are these (which use two of the many,
many predefined constants in VBA)...

> Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white

Range("C34:E34").Font.Color = vbWhite

> Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black

Range("C34:E34").Font.Color = vbBlack


Rick

Moving the .unprotect line helped:

Option Explicit
Private Sub CheckBox1_Click()
Me.Unprotect
If CheckBox1.Value = True Then
Me.Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
Else
Me.Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
End If
Me.Protect
End Sub

The me keyword refers to the worksheet that owns the code.



Khalil Handal wrote:
>
> Hi,
> When I clicked the box the first time it worked fine.
> Then I clicked it again (a second time) and had the following error message:
> Unable to set the color property of the font class
> any ideas!!!!
>
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> news(E-Mail Removed)...
> > Private Sub CheckBox1_Click()
> > If CheckBox1.Value = True Then
> > ActiveSheet.UnProtect
> > Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
> > Else
> > Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
> > End If
> > ActiveSheet.Protect
> > End Sub
> >
> >
> > Gord Dibben MS Excel MVP
> >
> > On Tue, 19 Jun 2007 16:52:48 +0300, "Khalil Handal" <(E-Mail Removed)>
> > wrote:
> >
> >>Hi,
> >>My sheet is protected and the cells are protected.
> >>It does not work in this case what should I do????
> >>
> >>
> >>"FSt1" <(E-Mail Removed)> wrote in message
> >>news:21622319-45AD-4D69-B411-(E-Mail Removed)...
> >>> hi
> >>> Private Sub CheckBox1_Click()
> >>> If CheckBox1.Value = True Then
> >>> Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
> >>> Else
> >>> Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
> >>> End If
> >>> End Sub
> >>>
> >>> regards
> >>> FSt1
> >>>
> >>> "Khalil Handal" wrote:
> >>>
> >>>> Hello to everybody,
> >>>> I have a sheet for showing a certificate of marks. Sometimes Ithe sum
> >>>> of
> >>>> the
> >>>> marks need to be shown when printing the certificate, and sometime I n
> >>>> don't
> >>>> need to see the sum.
> >>>> I have a checkBox named chekcbox1 on the sheet. What I need is a VBA
> >>>> code
> >>>> so
> >>>> that when the box is checked to hide the contents of the range C34:E34
> >>>> which
> >>>> containes the sum of the marks.
> >>>> PS. by "hiding" I mean "not to be seen. (maybe changing their color to
> >>>> white!!)
> >>>> Can anyone help in writing the code for me?
> >>>>
> >>>>
> >>>>
> >>
> >

--

Dave Peterson

Thanks a lot to all of you.
It worked just fine

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Moving the .unprotect line helped:
>
> Option Explicit
> Private Sub CheckBox1_Click()
> Me.Unprotect
> If CheckBox1.Value = True Then
> Me.Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
> Else
> Me.Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
> End If
> Me.Protect
> End Sub
>
> The me keyword refers to the worksheet that owns the code.
>
>
>
> Khalil Handal wrote:
>>
>> Hi,
>> When I clicked the box the first time it worked fine.
>> Then I clicked it again (a second time) and had the following error
>> message:
>> Unable to set the color property of the font class
>> any ideas!!!!
>>
>> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
>> news(E-Mail Removed)...
>> > Private Sub CheckBox1_Click()
>> > If CheckBox1.Value = True Then
>> > ActiveSheet.UnProtect
>> > Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
>> > Else
>> > Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
>> > End If
>> > ActiveSheet.Protect
>> > End Sub
>> >
>> >
>> > Gord Dibben MS Excel MVP
>> >
>> > On Tue, 19 Jun 2007 16:52:48 +0300, "Khalil Handal"
>> > <(E-Mail Removed)>
>> > wrote:
>> >
>> >>Hi,
>> >>My sheet is protected and the cells are protected.
>> >>It does not work in this case what should I do????
>> >>
>> >>
>> >>"FSt1" <(E-Mail Removed)> wrote in message
>> >>news:21622319-45AD-4D69-B411-(E-Mail Removed)...
>> >>> hi
>> >>> Private Sub CheckBox1_Click()
>> >>> If CheckBox1.Value = True Then
>> >>> Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
>> >>> Else
>> >>> Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
>> >>> End If
>> >>> End Sub
>> >>>
>> >>> regards
>> >>> FSt1
>> >>>
>> >>> "Khalil Handal" wrote:
>> >>>
>> >>>> Hello to everybody,
>> >>>> I have a sheet for showing a certificate of marks. Sometimes Ithe
>> >>>> sum
>> >>>> of
>> >>>> the
>> >>>> marks need to be shown when printing the certificate, and sometime I
>> >>>> n
>> >>>> don't
>> >>>> need to see the sum.
>> >>>> I have a checkBox named chekcbox1 on the sheet. What I need is a VBA
>> >>>> code
>> >>>> so
>> >>>> that when the box is checked to hide the contents of the range
>> >>>> C34:E34
>> >>>> which
>> >>>> containes the sum of the marks.
>> >>>> PS. by "hiding" I mean "not to be seen. (maybe changing their color
>> >>>> to
>> >>>> white!!)
>> >>>> Can anyone help in writing the code for me?
>> >>>>
>> >>>>
>> >>>>
>> >>
>> >
>
> --
>
> Dave Peterson

Yours also worked well; it gave the same result.
thanks

"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
> Even more descriptive (in my opinion) are these (which use two of the
> many, many predefined constants in VBA)...
>
>> Range("C34:E34").Font.Color = RGB(255, 255, 255) ' white
>
> Range("C34:E34").Font.Color = vbWhite
>
>> Range("C34:E34").Font.Color = RGB(0, 0, 0) 'black
>
> Range("C34:E34").Font.Color = vbBlack
>
>
> Rick