| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub name issue
Hi everyone I'm a bit puzzled over where to put some code to make it work.
I have got a public sub in my excel file. I know how you should call this in code but I do not know if this is the correct place for it. I only want the contents of the code to work on one sheet and I only would like the code to apply to columns Y, Z, AA, AB I dont know what the declaration name should be like onactivate or onchange. can someone help please. Patrick Malloy, I couldn't find your code you recommeded before. Thankyou for your help. Rob Here is a copy of the code in the sub Public Sub ConditionalFill() Dim val As Long Dim nr1 As Long Dim nr2 As Long Dim nr3 As Long Dim nr4 As Long Dim nr5 As Long Dim nr6 As Long Dim nr7 As Long Dim nr8 As Long val = ActiveCell.Value nr1 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).Va lue nr2 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1b")).Va lue nr3 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2a")).Va lue nr4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Va lue nr5 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3a")).Va lue nr6 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3b")).Va lue nr7 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4a")).Va lue nr8 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4b")).Va lue Select Case True Case val nr1 And val < nr2 ActiveCell.Interior.ColorIndex = 45 Case val nr3 And val < nr4 ActiveCell.Interior.ColorIndex = 3 Case val nr5 And val < nr6 ActiveCell.Interior.ColorIndex = 45 Case val nr7 And val < nr8 ActiveCell.Interior.ColorIndex = 3 Case Else MsgBox ("Non Apply"), vbInformation ActiveCell.Interior.ColorIndex = xlColorIndexNone End Select End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sub name issue
I am thinking the Workbook_SheetSelectionChange event is the place, have you
tried this? sh is one of the args, you test for the sh.name = "xxx" and you have code affecting only one sheet. Target is another arg, and it will be the cell that was GONE TO ... so, if you only want it to act on the cell you just finished working in, you can hold a static value called CELL_LAST_VISITED at the end of the sub. Then do your test on either Target or CELL_LAST_VISITED depending on your needs. "Robert Hargreaves" wrote in message ... Hi everyone I'm a bit puzzled over where to put some code to make it work. I have got a public sub in my excel file. I know how you should call this in code but I do not know if this is the correct place for it. I only want the contents of the code to work on one sheet and I only would like the code to apply to columns Y, Z, AA, AB I dont know what the declaration name should be like onactivate or onchange. can someone help please. Patrick Malloy, I couldn't find your code you recommeded before. Thankyou for your help. Rob Here is a copy of the code in the sub Public Sub ConditionalFill() Dim val As Long Dim nr1 As Long Dim nr2 As Long Dim nr3 As Long Dim nr4 As Long Dim nr5 As Long Dim nr6 As Long Dim nr7 As Long Dim nr8 As Long val = ActiveCell.Value nr1 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).Va lue nr2 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1b")).Va lue nr3 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2a")).Va lue nr4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Va lue nr5 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3a")).Va lue nr6 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3b")).Va lue nr7 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4a")).Va lue nr8 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4b")).Va lue Select Case True Case val nr1 And val < nr2 ActiveCell.Interior.ColorIndex = 45 Case val nr3 And val < nr4 ActiveCell.Interior.ColorIndex = 3 Case val nr5 And val < nr6 ActiveCell.Interior.ColorIndex = 45 Case val nr7 And val < nr8 ActiveCell.Interior.ColorIndex = 3 Case Else MsgBox ("Non Apply"), vbInformation ActiveCell.Interior.ColorIndex = xlColorIndexNone End Select End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sub name issue
You posted this same request message twice, but with a changed subject, even
though you had answers from other people (which you didn't acknowledge) under your earlier the thread ... "Declaration Name" ...Why ??? "Robert Hargreaves" wrote in message ... Hi everyone I'm a bit puzzled over where to put some code to make it work. I have got a public sub in my excel file. I know how you should call this in code but I do not know if this is the correct place for it. I only want the contents of the code to work on one sheet and I only would like the code to apply to columns Y, Z, AA, AB I dont know what the declaration name should be like onactivate or onchange. can someone help please. Patrick Malloy, I couldn't find your code you recommeded before. Thankyou for your help. Rob Here is a copy of the code in the sub Public Sub ConditionalFill() Dim val As Long Dim nr1 As Long Dim nr2 As Long Dim nr3 As Long Dim nr4 As Long Dim nr5 As Long Dim nr6 As Long Dim nr7 As Long Dim nr8 As Long val = ActiveCell.Value nr1 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).Va lue nr2 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1b")).Va lue nr3 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2a")).Va lue nr4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Va lue nr5 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3a")).Va lue nr6 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3b")).Va lue nr7 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4a")).Va lue nr8 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4b")).Va lue Select Case True Case val nr1 And val < nr2 ActiveCell.Interior.ColorIndex = 45 Case val nr3 And val < nr4 ActiveCell.Interior.ColorIndex = 3 Case val nr5 And val < nr6 ActiveCell.Interior.ColorIndex = 45 Case val nr7 And val < nr8 ActiveCell.Interior.ColorIndex = 3 Case Else MsgBox ("Non Apply"), vbInformation ActiveCell.Interior.ColorIndex = xlColorIndexNone End Select End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sub name issue
Please see replies in previous post - and please avoid duplicate posts.
"Robert Hargreaves" wrote: Hi everyone I'm a bit puzzled over where to put some code to make it work. I have got a public sub in my excel file. I know how you should call this in code but I do not know if this is the correct place for it. I only want the contents of the code to work on one sheet and I only would like the code to apply to columns Y, Z, AA, AB I dont know what the declaration name should be like onactivate or onchange. can someone help please. Patrick Malloy, I couldn't find your code you recommeded before. Thankyou for your help. Rob Here is a copy of the code in the sub Public Sub ConditionalFill() Dim val As Long Dim nr1 As Long Dim nr2 As Long Dim nr3 As Long Dim nr4 As Long Dim nr5 As Long Dim nr6 As Long Dim nr7 As Long Dim nr8 As Long val = ActiveCell.Value nr1 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).Va lue nr2 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1b")).Va lue nr3 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2a")).Va lue nr4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Va lue nr5 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3a")).Va lue nr6 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3b")).Va lue nr7 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4a")).Va lue nr8 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4b")).Va lue Select Case True Case val nr1 And val < nr2 ActiveCell.Interior.ColorIndex = 45 Case val nr3 And val < nr4 ActiveCell.Interior.ColorIndex = 3 Case val nr5 And val < nr6 ActiveCell.Interior.ColorIndex = 45 Case val nr7 And val < nr8 ActiveCell.Interior.ColorIndex = 3 Case Else MsgBox ("Non Apply"), vbInformation ActiveCell.Interior.ColorIndex = xlColorIndexNone End Select End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sub name issue
Sorry for the repeats I am using outlook express and I havent had the
problems before but couldnt see the old post in the list and now no matter what I refresh or reset doesnt work!! Thanks for the other replies though I will get them somehow. Rob "K Dales" wrote in message ... Please see replies in previous post - and please avoid duplicate posts. "Robert Hargreaves" wrote: Hi everyone I'm a bit puzzled over where to put some code to make it work. I have got a public sub in my excel file. I know how you should call this in code but I do not know if this is the correct place for it. I only want the contents of the code to work on one sheet and I only would like the code to apply to columns Y, Z, AA, AB I dont know what the declaration name should be like onactivate or onchange. can someone help please. Patrick Malloy, I couldn't find your code you recommeded before. Thankyou for your help. Rob Here is a copy of the code in the sub Public Sub ConditionalFill() Dim val As Long Dim nr1 As Long Dim nr2 As Long Dim nr3 As Long Dim nr4 As Long Dim nr5 As Long Dim nr6 As Long Dim nr7 As Long Dim nr8 As Long val = ActiveCell.Value nr1 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).Va lue nr2 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1b")).Va lue nr3 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2a")).Va lue nr4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Va lue nr5 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3a")).Va lue nr6 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3b")).Va lue nr7 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4a")).Va lue nr8 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4b")).Va lue Select Case True Case val nr1 And val < nr2 ActiveCell.Interior.ColorIndex = 45 Case val nr3 And val < nr4 ActiveCell.Interior.ColorIndex = 3 Case val nr5 And val < nr6 ActiveCell.Interior.ColorIndex = 45 Case val nr7 And val < nr8 ActiveCell.Interior.ColorIndex = 3 Case Else MsgBox ("Non Apply"), vbInformation ActiveCell.Interior.ColorIndex = xlColorIndexNone End Select End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sub name issue
I understand; I find I need to go to page 2 and back to page 1 to get it to
really refresh - probably something to do with the page being cached. "Rob Hargreaves" wrote: Sorry for the repeats I am using outlook express and I havent had the problems before but couldnt see the old post in the list and now no matter what I refresh or reset doesnt work!! Thanks for the other replies though I will get them somehow. Rob "K Dales" wrote in message ... Please see replies in previous post - and please avoid duplicate posts. "Robert Hargreaves" wrote: Hi everyone I'm a bit puzzled over where to put some code to make it work. I have got a public sub in my excel file. I know how you should call this in code but I do not know if this is the correct place for it. I only want the contents of the code to work on one sheet and I only would like the code to apply to columns Y, Z, AA, AB I dont know what the declaration name should be like onactivate or onchange. can someone help please. Patrick Malloy, I couldn't find your code you recommeded before. Thankyou for your help. Rob Here is a copy of the code in the sub Public Sub ConditionalFill() Dim val As Long Dim nr1 As Long Dim nr2 As Long Dim nr3 As Long Dim nr4 As Long Dim nr5 As Long Dim nr6 As Long Dim nr7 As Long Dim nr8 As Long val = ActiveCell.Value nr1 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).Va lue nr2 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1b")).Va lue nr3 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2a")).Va lue nr4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Va lue nr5 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3a")).Va lue nr6 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3b")).Va lue nr7 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4a")).Va lue nr8 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4b")).Va lue Select Case True Case val nr1 And val < nr2 ActiveCell.Interior.ColorIndex = 45 Case val nr3 And val < nr4 ActiveCell.Interior.ColorIndex = 3 Case val nr5 And val < nr6 ActiveCell.Interior.ColorIndex = 45 Case val nr7 And val < nr8 ActiveCell.Interior.ColorIndex = 3 Case Else MsgBox ("Non Apply"), vbInformation ActiveCell.Interior.ColorIndex = xlColorIndexNone End Select End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sub name issue
http://groups.google.co.uk/advanced_group_search?hl=en
search on this group microsoft.public.excel.programming and your email address as author. Restrict the date to today. -- Regards, Tom Ogilvy "Rob Hargreaves" wrote in message ... Sorry for the repeats I am using outlook express and I havent had the problems before but couldnt see the old post in the list and now no matter what I refresh or reset doesnt work!! Thanks for the other replies though I will get them somehow. Rob "K Dales" wrote in message ... Please see replies in previous post - and please avoid duplicate posts. "Robert Hargreaves" wrote: Hi everyone I'm a bit puzzled over where to put some code to make it work. I have got a public sub in my excel file. I know how you should call this in code but I do not know if this is the correct place for it. I only want the contents of the code to work on one sheet and I only would like the code to apply to columns Y, Z, AA, AB I dont know what the declaration name should be like onactivate or onchange. can someone help please. Patrick Malloy, I couldn't find your code you recommeded before. Thankyou for your help. Rob Here is a copy of the code in the sub Public Sub ConditionalFill() Dim val As Long Dim nr1 As Long Dim nr2 As Long Dim nr3 As Long Dim nr4 As Long Dim nr5 As Long Dim nr6 As Long Dim nr7 As Long Dim nr8 As Long val = ActiveCell.Value nr1 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).Va lue nr2 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1b")).Va lue nr3 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2a")).Va lue nr4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Va lue nr5 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3a")).Va lue nr6 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3b")).Va lue nr7 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4a")).Va lue nr8 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4b")).Va lue Select Case True Case val nr1 And val < nr2 ActiveCell.Interior.ColorIndex = 45 Case val nr3 And val < nr4 ActiveCell.Interior.ColorIndex = 3 Case val nr5 And val < nr6 ActiveCell.Interior.ColorIndex = 45 Case val nr7 And val < nr8 ActiveCell.Interior.ColorIndex = 3 Case Else MsgBox ("Non Apply"), vbInformation ActiveCell.Interior.ColorIndex = xlColorIndexNone End Select End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum issue | Excel Worksheet Functions | |||
| issue.... | Excel Discussion (Misc queries) | |||
| Issue with zero/# issue!! | Excel Worksheet Functions | |||
| With and End With issue | Excel Programming | |||
| VBE Issue | Excel Programming | |||
![Robert Hargreaves[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
