Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Conditional formatting of a Command buttton
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default Conditional formatting of a Command buttton
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 QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Conditional formatting of a Command buttton
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 QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 56
Default Conditional formatting of a Command buttton
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 QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Conditional formatting of a Command buttton
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 QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default Conditional formatting of a Command buttton
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 QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Conditional formatting of a Command buttton
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 QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Conditional formatting of a Command buttton
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 QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default Conditional formatting of a Command buttton
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 QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default Conditional formatting of a Command buttton
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 QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default Conditional formatting of a Command buttton
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 QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default Conditional formatting of a Command buttton
Sorry, make that 2 questions

So, here's what I have now

<====================
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 = vbBlack
.Font.Size = 10
Case True
.ForeColor = vbRed
.Font.Size = 10
.Font.Style = Bold
Case False
.ForeColor = vbBlack
.Font.Size = 10
End Select
End With
End If
End Sub
<=====================

when R1 is blank or FALSE the font is size 10 black, when R1 changes to TRUE
the font changes to size 10 red (not bold) but when R1 changes back to blank
or FALSE the font stays red.

I figured that "Private Sub Worksheet_Change(ByVal Target As Range)" the
_Change would also change it back to black.

Is there an easy way to accomplish that?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200706/1

Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Conditional formatting of a Command buttton
Hi BlackBox,

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 = vbBlack
With .Font
.Size = 10
.Bold = False
End With
Case True
.ForeColor = vbRed
With .Font
.Size = 10
.Bold = True
End With
Case False
.ForeColor = vbBlack
With .Font
.Size = 10
.Bold = False
End With
End Select
End With
End If
End Sub
' <=====================

Or. perhaps, more simply:

'<====================
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 True
.ForeColor = vbRed
With .Font
.Size = 10
.Bold = True
End With
Case Else
.ForeColor = vbBlack
With .Font
.Size = 10
.Bold = False
End With
End Select
End With
End If
End Sub
' <=====================


---
Regards,
Norman

"blackbox via OfficeKB.com" <u20390@uwe wrote in message
news:733cf436dbda3@uwe...
Sorry, make that 2 questions

So, here's what I have now

<====================
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 = vbBlack
.Font.Size = 10
Case True
.ForeColor = vbRed
.Font.Size = 10
.Font.Style = Bold
Case False
.ForeColor = vbBlack
.Font.Size = 10
End Select
End With
End If
End Sub
<=====================

when R1 is blank or FALSE the font is size 10 black, when R1 changes to
TRUE
the font changes to size 10 red (not bold) but when R1 changes back to
blank
or FALSE the font stays red.

I figured that "Private Sub Worksheet_Change(ByVal Target As Range)" the
_Change would also change it back to black.

Is there an easy way to accomplish that?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200706/1



Reply With QuoteReply With Quote
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default Conditional formatting of a Command buttton
still having problems getting it to switch back and forth

Thanks again for all your help

Norman Jones wrote:
Hi BlackBox,

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 = vbBlack
With .Font
.Size = 10
.Bold = False
End With
Case True
.ForeColor = vbRed
With .Font
.Size = 10
.Bold = True
End With
Case False
.ForeColor = vbBlack
With .Font
.Size = 10
.Bold = False
End With
End Select
End With
End If
End Sub
' <=====================

Or. perhaps, more simply:

'<====================
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 True
.ForeColor = vbRed
With .Font
.Size = 10
.Bold = True
End With
Case Else
.ForeColor = vbBlack
With .Font
.Size = 10
.Bold = False
End With
End Select
End With
End If
End Sub
' <=====================

---
Regards,
Norman

Sorry, make that 2 questions

[quoted text clipped - 35 lines]

Is there an easy way to accomplish that?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200706/1

Reply With QuoteReply With Quote
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Conditional formatting of a Command buttton
Hi BlackBox,

The code works for me without problem.

If you wish, I can send you my test file, in response to
an e-mail:

norman_jones@NOSPAMbtconnectDOTcom

(Delete "NOSPAM" and replace "DOT" with a full stop [period] )



---
Regards,
Norman

"blackbox via OfficeKB.com" <u20390@uwe wrote in message
news:733dd07877d28@uwe...
still having problems getting it to switch back and forth

Thanks again for all your help

Norman Jones wrote:
Hi BlackBox,


Reply With QuoteReply With Quote
  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default Conditional formatting of a Command buttton
sent you an email



Norman Jones wrote:
Hi BlackBox,

The code works for me without problem.

If you wish, I can send you my test file, in response to
an e-mail:

norman_jones@NOSPAMbtconnectDOTcom

(Delete "NOSPAM" and replace "DOT" with a full stop [period] )

---
Regards,
Norman

still having problems getting it to switch back and forth

Thanks again for all your help

Hi BlackBox,

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200706/1

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect Cell Formatting including Conditional Formatting Mick Jennings Excel Discussion (Misc queries) 5 November 13th 07 05:32 PM
IF Command conditional formating IDCrewDawg Excel Worksheet Functions 7 September 8th 05 04:19 PM
Is there a command like subtotal but with a conditional? Hiughs Excel Worksheet Functions 1 March 1st 05 07:24 PM
formatting text/number in a command mnarvind[_2_] Excel Programming 0 October 14th 04 12:42 PM
formatting text/number in a command mnarvind Excel Programming 1 October 12th 04 01:10 PM


All times are GMT +1. The time now is 08:11 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement