PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Conditional formatting of a Command buttton

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

Conditional formatting of a Command buttton

 
 
blackbox via OfficeKB.com
Guest
Posts: n/a
 
      5th Jun 2007
Hello,

I want to change the font (size & color) of a command button caption based of
the value of a cell (true or false).

I was thinking maybe something like this but not sure about the syntax.

Select Case Range("R1").Value
Case Is = "True"
Reset.Font = "Red"
Reset.Font = Size 12 ???
End Select


Below is the command button code. I wasn't sure where the font change
isntructions should go. Can it go anywhere in the Sub?

Private Sub Reset_Click()
Range("R3").Value = "False"
Range("R6").Value = "False"
Range("R10").Value = "False"
Range("R14").Value = "False"
Range("R17").Value = "False"
Range("R23").Value = "False"
Range("R26").Value = "False"
Range("R28").Value = "False"
Range("R31").Value = "False"
Range("R33").Value = "False"
Range("R35").Value = "False"
Range("R41").Value = "False"
Range("R46").Value = "False"
Range("R48").Value = "False"
Range("R51").Value = "False"
Range("R55").Value = "False"
Range("R65").Value = "False"
Range("R69").Value = "False"
Range("R73").Value = "False"
Range("R77").Value = "False"
Range("R79").Value = "False"
Range("R82").Value = "False"
Range("R86").Value = "False"
Range("R93").Value = "False"
Range("R97").Value = "False"
ActiveWindow.ScrollRow = 2
Columns("G:G").Select
Selection.ClearContents
Range("F20:F21").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 1
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200706/1
 
Reply With Quote
 
 
 
 
Norman Jones
Guest
Posts: n/a
 
      5th Jun 2007
Hi BlackBox,

Perhaps try something like:

'=============>>
Private Sub Reset_Click()
Range("R3,R6,R102,R14,R17,R23,R26,R28,R31," _
& "R33,R35,R41,R46,R48,R51,R55,R65,R69," _
& "R73,R77,R79,R82,R86,R93,R97").Value = "False"

Range("G:G,F20:F21").ClearContents

With Me.CommandButton1
Select Case Me.Range("R1").Value
Case vbNullString
.ForeColor = &HFF00FF
.Font.Size = 12
Case True
.ForeColor = &H8080FF
.Font.Size = 16
Case False
.ForeColor = &HFF0000
.Font.Size = 12

End Select
End With
End Sub
'<<=============


---
Regards,
Norman

"blackbox via OfficeKB.com" <u20390@uwe> wrote in message
news:733b5711efa51@uwe...
> Hello,
>
> I want to change the font (size & color) of a command button caption based
> of
> the value of a cell (true or false).
>
> I was thinking maybe something like this but not sure about the syntax.
>
> Select Case Range("R1").Value
> Case Is = "True"
> Reset.Font = "Red"
> Reset.Font = Size 12 ???
> End Select
>
>
> Below is the command button code. I wasn't sure where the font change
> isntructions should go. Can it go anywhere in the Sub?
>
> Private Sub Reset_Click()
> Range("R3").Value = "False"
> Range("R6").Value = "False"
> Range("R10").Value = "False"
> Range("R14").Value = "False"
> Range("R17").Value = "False"
> Range("R23").Value = "False"
> Range("R26").Value = "False"
> Range("R28").Value = "False"
> Range("R31").Value = "False"
> Range("R33").Value = "False"
> Range("R35").Value = "False"
> Range("R41").Value = "False"
> Range("R46").Value = "False"
> Range("R48").Value = "False"
> Range("R51").Value = "False"
> Range("R55").Value = "False"
> Range("R65").Value = "False"
> Range("R69").Value = "False"
> Range("R73").Value = "False"
> Range("R77").Value = "False"
> Range("R79").Value = "False"
> Range("R82").Value = "False"
> Range("R86").Value = "False"
> Range("R93").Value = "False"
> Range("R97").Value = "False"
> ActiveWindow.ScrollRow = 2
> Columns("G:G").Select
> Selection.ClearContents
> Range("F20:F21").Select
> Selection.ClearContents
> ActiveWindow.ScrollColumn = 1
> End Sub
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200706/1
>
 
Reply With Quote
 
=?Utf-8?B?QmVuIE1jQmVu?=
Guest
Posts: n/a
 
      5th Jun 2007
BTW - hopefully not gilding the Lilly of Normans excelent solution, VBA has
some colours predefined as constants so instead of "&HFF00FF" you can
substitute "vbMagenta" (no quotes)

Constant Value Description
vbBlack 0x0 Black
vbRed 0xFF Red
vbGreen 0xFF00 Green
vbYellow 0xFFFF Yellow
vbBlue 0xFF0000 Blue
vbMagenta 0xFF00FF Magenta
vbCyan 0xFFFF00 Cyan
vbWhite 0xFFFFFF White





"blackbox via OfficeKB.com" wrote:

> Hello,
>
> I want to change the font (size & color) of a command button caption based of
> the value of a cell (true or false).
>
> I was thinking maybe something like this but not sure about the syntax.
>
> Select Case Range("R1").Value
> Case Is = "True"
> Reset.Font = "Red"
> Reset.Font = Size 12 ???
> End Select
>
>
> Below is the command button code. I wasn't sure where the font change
> isntructions should go. Can it go anywhere in the Sub?
>
> Private Sub Reset_Click()
> Range("R3").Value = "False"
> Range("R6").Value = "False"
> Range("R10").Value = "False"
> Range("R14").Value = "False"
> Range("R17").Value = "False"
> Range("R23").Value = "False"
> Range("R26").Value = "False"
> Range("R28").Value = "False"
> Range("R31").Value = "False"
> Range("R33").Value = "False"
> Range("R35").Value = "False"
> Range("R41").Value = "False"
> Range("R46").Value = "False"
> Range("R48").Value = "False"
> Range("R51").Value = "False"
> Range("R55").Value = "False"
> Range("R65").Value = "False"
> Range("R69").Value = "False"
> Range("R73").Value = "False"
> Range("R77").Value = "False"
> Range("R79").Value = "False"
> Range("R82").Value = "False"
> Range("R86").Value = "False"
> Range("R93").Value = "False"
> Range("R97").Value = "False"
> ActiveWindow.ScrollRow = 2
> Columns("G:G").Select
> Selection.ClearContents
> Range("F20:F21").Select
> Selection.ClearContents
> ActiveWindow.ScrollColumn = 1
> End Sub
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200706/1
>
>
 
Reply With Quote
 
Norman Jones
Guest
Posts: n/a
 
      5th Jun 2007
Hi BlackBox,

> With Me.CommandButton1

Should read:

With Me.Reset_Click

(I used a default name and forgot to amend my code to
reflect your button's name!)



---
Regards,
Norman
 
Reply With Quote
 
blackbox via OfficeKB.com
Guest
Posts: n/a
 
      5th Jun 2007
Thanks for the quick responses.

Guess I didn't think my first post through very well.

I don't want the font change activated by the "Reset" button but to change
automatically when R1 equals "True"

I tried the following but not sure how to activate the font change portion

Private Sub Reset_Click()
Range("R3,R6,R10,R14,R17,R23,R26,R28,R31," _
& "R33,R35,R41,R46,R48,R51,R55,R65,R69," _
& "R73,R77,R79,R82,R86,R93,R97").Value = "False"

Range("G:G,F20:F21").ClearContents
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1

End Sub
------------------------------------------------------------------------------
-----
Private Sub Font()
With Me.Reset
Select Case Me.Range("R1").Value
Case vbNullString
.ForeColor = &HFF00FF
.Font.Size = 12
Case True
.ForeColor = &H8080FF
.Font.Size = 16
Case False
.ForeColor = &HFF0000
.Font.Size = 12
End Select
End With
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200706/1
 
Reply With Quote
 
Norman Jones
Guest
Posts: n/a
 
      5th Jun 2007
Hi Blackbox,

In the worksheet module, try:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range

Set Rng = Me.Range("R1")

If Not Intersect(Rng, Target) Is Nothing Then
With Me.Reset
Select Case Me.Range("R1").Value
Case vbNullString
.ForeColor = &HFF00FF
.Font.Size = 12
Case True
.ForeColor = &H8080FF
.Font.Size = 16
Case False
.ForeColor = &HFF0000
.Font.Size = 12
End Select
End With
End If
End Sub
'<<=============


---
Regards,
Norman

"blackbox via OfficeKB.com" <u20390@uwe> wrote in message
news:733c397b5a56e@uwe...
> Thanks for the quick responses.
>
> Guess I didn't think my first post through very well.
>
> I don't want the font change activated by the "Reset" button but to change
> automatically when R1 equals "True"
>
> I tried the following but not sure how to activate the font change portion
>
> Private Sub Reset_Click()
> Range("R3,R6,R10,R14,R17,R23,R26,R28,R31," _
> & "R33,R35,R41,R46,R48,R51,R55,R65,R69," _
> & "R73,R77,R79,R82,R86,R93,R97").Value = "False"
>
> Range("G:G,F20:F21").ClearContents
> ActiveWindow.ScrollRow = 1
> ActiveWindow.ScrollColumn = 1
>
> End Sub
> ------------------------------------------------------------------------------
> -----
> Private Sub Font()
> With Me.Reset
> Select Case Me.Range("R1").Value
> Case vbNullString
> .ForeColor = &HFF00FF
> .Font.Size = 12
> Case True
> .ForeColor = &H8080FF
> .Font.Size = 16
> Case False
> .ForeColor = &HFF0000
> .Font.Size = 12
> End Select
> End With
> End Sub
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200706/1
>
 
Reply With Quote
 
Norman Jones
Guest
Posts: n/a
 
      5th Jun 2007
Hi BlackBox,

Given your subsequent post, it is no longer of relevance,
but there is a typo:

> With Me.Reset_Click

should, of course, have been:

With Me.Reset


---
Regards,
Norman
 
Reply With Quote
 
blackbox via OfficeKB.com
Guest
Posts: n/a
 
      5th Jun 2007
Hi Norman,

The Reset command button is in Sheet 1

Should I move it to This Workbook or can I do something like "With Me.sheet 1.
Reset"

--
Message posted via http://www.officekb.com
 
Reply With Quote
 
blackbox via OfficeKB.com
Guest
Posts: n/a
 
      5th Jun 2007
Nevermind, I got it.

I put it in the wrong module.


Thanks for all the help!

blackbox wrote:
>Hi Norman,
>
>The Reset command button is in Sheet 1
>
>Should I move it to This Workbook or can I do something like "With Me.sheet 1.
>Reset"

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200706/1
 
Reply With Quote
 
blackbox via OfficeKB.com
Guest
Posts: n/a
 
      5th Jun 2007
Iv'e got 1 more question

tried

.Font.Style = Bold

didn't work, I guess that's not the right syntax?

--
Message posted via http://www.officekb.com
 
Reply With Quote
 
 
 
Reply
Page 1 of 2 1 2

« How can I test in VBA what edition of Office a user is running | Excel 2003> how to export to DBF with a field of fraction? »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Command buttton doesn't find fields in parent form =?Utf-8?B?QWNjZXNzQ29tcA==?= Microsoft Access Macros 7 2nd Oct 2007 11:58 PM
Command buttton code for addding data in an Access 2002XP table Geo Microsoft Access Macros 2 22nd Aug 2004 01:30 AM
Command buttton code for addding data in an Access 2002XP table Geo Microsoft Access VBA Modules 2 22nd Aug 2004 01:30 AM
Command buttton code for addding data in an Access 2002XP table Geo Microsoft Access Form Coding 2 22nd Aug 2004 01:30 AM
Command buttton code for addding data in a table Geo Microsoft Access 1 22nd Aug 2004 12:45 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 06:35 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top