| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA
I have these macro's built to run on my (Lookup) Sheet1. My trouble is that
they work fine when I play each individual macro; however, I cannot get them to run automatically. My security is set to low and I have 'Trust all installed add-ins and templates' checked. MS Excel 2003 Thanks, Todd Sub Color_G6() Dim Num As Long Dim rng As Range Set rng = Me.Range("G6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Sub Color_J6() Dim Num As Long Dim rng As Range Set rng = Me.Range("J6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Sub Color_K6() Dim Num As Long Dim rng As Range Set rng = Me.Range("K6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA
I'm guessing you want to use a Worksheet Change event. You can read more
about events here http://www.cpearson.com/excel/Events.aspx -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "FP Novice" wrote: I have these macro's built to run on my (Lookup) Sheet1. My trouble is that they work fine when I play each individual macro; however, I cannot get them to run automatically. My security is set to low and I have 'Trust all installed add-ins and templates' checked. MS Excel 2003 Thanks, Todd Sub Color_G6() Dim Num As Long Dim rng As Range Set rng = Me.Range("G6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Sub Color_J6() Dim Num As Long Dim rng As Range Set rng = Me.Range("J6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Sub Color_K6() Dim Num As Long Dim rng As Range Set rng = Me.Range("K6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA
If that is indeed the case, right click sheet tabview codecopy/paste this
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("g6, j6, k6")) Is Nothing Then Exit Sub Dim Num As Double Application.EnableEvents = False 'Determine the color Select Case UCase(Target) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua Case Else Num = 0 End Select 'Apply the color Target.Font.ColorIndex = Num Application.EnableEvents = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Barb Reinhardt" wrote in message ... I'm guessing you want to use a Worksheet Change event. You can read more about events here http://www.cpearson.com/excel/Events.aspx -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "FP Novice" wrote: I have these macro's built to run on my (Lookup) Sheet1. My trouble is that they work fine when I play each individual macro; however, I cannot get them to run automatically. My security is set to low and I have 'Trust all installed add-ins and templates' checked. MS Excel 2003 Thanks, Todd Sub Color_G6() Dim Num As Long Dim rng As Range Set rng = Me.Range("G6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Sub Color_J6() Dim Num As Long Dim rng As Range Set rng = Me.Range("J6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Sub Color_K6() Dim Num As Long Dim rng As Range Set rng = Me.Range("K6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA
Thanks Barb, nice link and the Worksheet_Change worked...
"Barb Reinhardt" wrote: I'm guessing you want to use a Worksheet Change event. You can read more about events here http://www.cpearson.com/excel/Events.aspx -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "FP Novice" wrote: I have these macro's built to run on my (Lookup) Sheet1. My trouble is that they work fine when I play each individual macro; however, I cannot get them to run automatically. My security is set to low and I have 'Trust all installed add-ins and templates' checked. MS Excel 2003 Thanks, Todd Sub Color_G6() Dim Num As Long Dim rng As Range Set rng = Me.Range("G6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Sub Color_J6() Dim Num As Long Dim rng As Range Set rng = Me.Range("J6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Sub Color_K6() Dim Num As Long Dim rng As Range Set rng = Me.Range("K6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA
Perfect Don, much cleaner than what I had. I did leave the opening string:
Private Sub Worksheet_Change(ByVal Target As Range) The rest I converted to what you gave me, thanks. I do have another question, how can I make the macro work when G6, J6, and K6 are protected cells, since they hold functions I want protected. Thanks again, Todd "Don Guillett" wrote: Try it this way Sub Colorif() Dim c For Each c In Array("g6", "j6", "k6") Dim Num As Long Dim rng As Range Set rng = Range(c) ' Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua Case Else Num = 0 End Select 'Apply the color rng.Font.ColorIndex = Num 'Application.EnableEvents = True Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "FP Novice" wrote in message ... I have these macro's built to run on my (Lookup) Sheet1. My trouble is that they work fine when I play each individual macro; however, I cannot get them to run automatically. My security is set to low and I have 'Trust all installed add-ins and templates' checked. MS Excel 2003 Thanks, Todd Sub Color_G6() Dim Num As Long Dim rng As Range Set rng = Me.Range("G6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Sub Color_J6() Dim Num As Long Dim rng As Range Set rng = Me.Range("J6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Sub Color_K6() Dim Num As Long Dim rng As Range Set rng = Me.Range("K6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA
Show your FINAL code. A worksheet change event works on changing the cell so
it should be UN protected. -- Don Guillett Microsoft MVP Excel SalesAid Software "FP Novice" wrote in message ... Perfect Don, much cleaner than what I had. I did leave the opening string: Private Sub Worksheet_Change(ByVal Target As Range) The rest I converted to what you gave me, thanks. I do have another question, how can I make the macro work when G6, J6, and K6 are protected cells, since they hold functions I want protected. Thanks again, Todd "Don Guillett" wrote: Try it this way Sub Colorif() Dim c For Each c In Array("g6", "j6", "k6") Dim Num As Long Dim rng As Range Set rng = Range(c) ' Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua Case Else Num = 0 End Select 'Apply the color rng.Font.ColorIndex = Num 'Application.EnableEvents = True Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "FP Novice" wrote in message ... I have these macro's built to run on my (Lookup) Sheet1. My trouble is that they work fine when I play each individual macro; however, I cannot get them to run automatically. My security is set to low and I have 'Trust all installed add-ins and templates' checked. MS Excel 2003 Thanks, Todd Sub Color_G6() Dim Num As Long Dim rng As Range Set rng = Me.Range("G6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Sub Color_J6() Dim Num As Long Dim rng As Range Set rng = Me.Range("J6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Sub Color_K6() Dim Num As Long Dim rng As Range Set rng = Me.Range("K6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 2 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
