Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you have a macro always running on a sheet waiting for data
The subject line, pretty much sums it up. This is the macro I have, as I
input data I want it to change without executing the macro, (i.e, the purpose of the question.) Range("A1:A65536").Select Selection.Replace What:="39", Replacement:="0", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you have a macro always running on a sheet waiting for data
You might be interested in using event procedures, especially the
Worksheet_Change event. See http://www.cpearson.com/excel/events.htm for more information about events. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Sean" wrote in message ... The subject line, pretty much sums it up. This is the macro I have, as I input data I want it to change without executing the macro, (i.e, the purpose of the question.) Range("A1:A65536").Select Selection.Replace What:="39", Replacement:="0", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you have a macro always running on a sheet waiting for dat
Thanks Chip, I'll take a look however my idea of using this macro for my
first intention has backfired. I had the macro originally planned to do the reverse, all I want is a simple find any cell in range A1:A65536 and if its not equel to 99,66,33,11 then change it to a 0. Again this didnt work the way I thought it would I thought I could replace the ="39" with an < = ({"99","66","33","11"}). Well as you can guess it didnt work. Any ideas. "Chip Pearson" wrote: You might be interested in using event procedures, especially the Worksheet_Change event. See http://www.cpearson.com/excel/events.htm for more information about events. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Sean" wrote in message ... The subject line, pretty much sums it up. This is the macro I have, as I input data I want it to change without executing the macro, (i.e, the purpose of the question.) Range("A1:A65536").Select Selection.Replace What:="39", Replacement:="0", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you have a macro always running on a sheet waiting for dat
Sean,
Paste this code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:A65536")) Is Nothing Then On Error GoTo err_handler Application.EnableEvents = False Select Case Target.Value Case 99, 66, 33, 11 'do nothing Case Else Target.Value = 0 End Select End If err_handler: Application.EnableEvents = True End Sub I have a question regarding the above. Is there a simple coding above to have the four numbers be the first Case, so that I don't have the "do nothing" bit as the first case? hth, Doug "Sean" wrote in message ... Thanks Chip, I'll take a look however my idea of using this macro for my first intention has backfired. I had the macro originally planned to do the reverse, all I want is a simple find any cell in range A1:A65536 and if its not equel to 99,66,33,11 then change it to a 0. Again this didnt work the way I thought it would I thought I could replace the ="39" with an < = ({"99","66","33","11"}). Well as you can guess it didnt work. Any ideas. "Chip Pearson" wrote: You might be interested in using event procedures, especially the Worksheet_Change event. See http://www.cpearson.com/excel/events.htm for more information about events. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Sean" wrote in message ... The subject line, pretty much sums it up. This is the macro I have, as I input data I want it to change without executing the macro, (i.e, the purpose of the question.) Range("A1:A65536").Select Selection.Replace What:="39", Replacement:="0", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you have a macro always running on a sheet waiting for dat
It keeps giving me an error on it, am I suppose to paste in with my current
module or just start from scratch? Either way it does not seem to be working. Any help would be great. Thanks for the assistance so far.... Sean "Doug Glancy" wrote: Sean, Paste this code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:A65536")) Is Nothing Then On Error GoTo err_handler Application.EnableEvents = False Select Case Target.Value Case 99, 66, 33, 11 'do nothing Case Else Target.Value = 0 End Select End If err_handler: Application.EnableEvents = True End Sub I have a question regarding the above. Is there a simple coding above to have the four numbers be the first Case, so that I don't have the "do nothing" bit as the first case? hth, Doug "Sean" wrote in message ... Thanks Chip, I'll take a look however my idea of using this macro for my first intention has backfired. I had the macro originally planned to do the reverse, all I want is a simple find any cell in range A1:A65536 and if its not equel to 99,66,33,11 then change it to a 0. Again this didnt work the way I thought it would I thought I could replace the ="39" with an < = ({"99","66","33","11"}). Well as you can guess it didnt work. Any ideas. "Chip Pearson" wrote: You might be interested in using event procedures, especially the Worksheet_Change event. See http://www.cpearson.com/excel/events.htm for more information about events. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Sean" wrote in message ... The subject line, pretty much sums it up. This is the macro I have, as I input data I want it to change without executing the macro, (i.e, the purpose of the question.) Range("A1:A65536").Select Selection.Replace What:="39", Replacement:="0", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you have a macro always running on a sheet waiting for dat
Sean,
Paste just the code I wrote into the code module for that sheet. You can the module inside the VBE, or just right-click on the worksheet tab (in Excel) and choose "View Code." Don't use your old code. This code is a Change Event, like Chip referred to earlier. The event is fired every time you change a value in the worksheet. I realized that the code I gave before wouldn't work if you changed more than one cell at a time. This does (I think). I've commented the code below: Private Sub Worksheet_Change(ByVal Target As Range) Dim changed_column_A As Range Dim changed_cell As Range 'If the change was range was at least part in Column A If Not Intersect(Target, Columns(1)) Is Nothing Then 'If there's an error turn EnableEvents back on On Error GoTo err_handler 'Define the part of the changed range in Column A Set changed_column_A = Intersect(Target, Columns(1)) 'Turn off Events so that this Change doesn't trigger another Change event Application.EnableEvents = False 'Go through the changed range one cell at a time For Each changed_cell In changed_column_A 'Look at the possible changes to the changed range, called "Target" Select Case changed_cell.Value 'If the value of Target now equals one of your specified values Case 99, 66, 33, 11 'do nothing 'If the Target now equals any other value Case Else 'change Target to 0 Target.Value = 0 End Select Next changed_cell End If 'in case of an error EnableEvents is turned back on. 'The code is also executed if there is no error err_handler: Application.EnableEvents = True End Sub hth, Doug "Sean" wrote in message ... It keeps giving me an error on it, am I suppose to paste in with my current module or just start from scratch? Either way it does not seem to be working. Any help would be great. Thanks for the assistance so far.... Sean "Doug Glancy" wrote: Sean, Paste this code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:A65536")) Is Nothing Then 'was the change in that range? On Error GoTo err_handler Application.EnableEvents = False Select Case Target.Value 'Look at the possible changes to the changed range Case 99, 66, 33, 11 'If the value of the changed range equalled specified values 'do nothing Case Else 'If the value equalled anything else Target.Value = 0 'change it to 0 End Select End If err_handler: Application.EnableEvents = True End Sub I have a question regarding the above. Is there a simple coding above to have the four numbers be the first Case, so that I don't have the "do nothing" bit as the first case? hth, Doug "Sean" wrote in message ... Thanks Chip, I'll take a look however my idea of using this macro for my first intention has backfired. I had the macro originally planned to do the reverse, all I want is a simple find any cell in range A1:A65536 and if its not equel to 99,66,33,11 then change it to a 0. Again this didnt work the way I thought it would I thought I could replace the ="39" with an < = ({"99","66","33","11"}). Well as you can guess it didnt work. Any ideas. "Chip Pearson" wrote: You might be interested in using event procedures, especially the Worksheet_Change event. See http://www.cpearson.com/excel/events.htm for more information about events. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Sean" wrote in message ... The subject line, pretty much sums it up. This is the macro I have, as I input data I want it to change without executing the macro, (i.e, the purpose of the question.) Range("A1:A65536").Select Selection.Replace What:="39", Replacement:="0", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Waiting for Input in a macro | Excel Discussion (Misc queries) | |||
Waiting for data to be returned from Microsoft Query | Excel Discussion (Misc queries) | |||
Waiting Mode when query data from Db via VBA! | Excel Programming | |||
Running Macro on every sheet in Workbook !!! | Excel Programming | |||
waiting for data refresh before continuing VBA sub | Excel Programming |