Run a macro based on the value of a cell

Hi Everyone

I have what seems to be a simple task but can't seem to figure it out.  I 
have a excel file that has the tabs "Current User List" & "Prev User List"  
On these sheets the first row is called "status"  which is either "curr" or 
"prev" to indicate employee status.  I copied and modified the macro posted 
on this site for copying an entire row to another worsheet and it works fine 
but I'd like to go one step further.  If I change the status of an employee 
on the "Current User List" to "Prev" I want the macro to run so that the 
record is copied to the "Prev User List automatically.

Any help would be greatly apprciated.

0
Coyote (9)
11/19/2004 7:39:07 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
574 Views

Similar Articles

[PageSpeed] 18

Hi
then you need an event procedure. see:
http://www.cpearson.com/excel/events.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Coyote" <Coyote@discussions.microsoft.com> schrieb im Newsbeitrag
news:F1E0072A-048B-4D52-8D99-B6B6A22DEAF3@microsoft.com...
> Hi Everyone
>
> I have what seems to be a simple task but can't seem to figure it
out.  I
> have a excel file that has the tabs "Current User List" & "Prev User
List"
> On these sheets the first row is called "status"  which is either
"curr" or
> "prev" to indicate employee status.  I copied and modified the macro
posted
> on this site for copying an entire row to another worsheet and it
works fine
> but I'd like to go one step further.  If I change the status of an
employee
> on the "Current User List" to "Prev" I want the macro to run so that
the
> record is copied to the "Prev User List automatically.
>
> Any help would be greatly apprciated.
>

0
frank.kabel (11126)
11/19/2004 7:51:59 PM
Thanks Frank,
Look at my attached Code  In particular the Target.Address.  I want this 
routine exeuted anytime a change is made to column A regardless of which row 
is selected.  The code as it stands now will work but only if I select row 2 
column A.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$A2" Then Exit Sub
 Dim sourceRange As Range
    Dim destrange As Range
    Dim Lr As Long
    Lr = LastRow(Sheets("Overall List - Prev Users")) + 1
    Set sourceRange = ActiveCell.EntireRow
    Set destrange = Sheets("Overall List - Prev Users").Rows(Lr)
    sourceRange.Copy destrange
    sourceRange.EntireRow.Delete

End Sub
Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function


"Frank Kabel" wrote:

> Hi
> then you need an event procedure. see:
> http://www.cpearson.com/excel/events.htm
> 
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
> 
> "Coyote" <Coyote@discussions.microsoft.com> schrieb im Newsbeitrag
> news:F1E0072A-048B-4D52-8D99-B6B6A22DEAF3@microsoft.com...
> > Hi Everyone
> >
> > I have what seems to be a simple task but can't seem to figure it
> out.  I
> > have a excel file that has the tabs "Current User List" & "Prev User
> List"
> > On these sheets the first row is called "status"  which is either
> "curr" or
> > "prev" to indicate employee status.  I copied and modified the macro
> posted
> > on this site for copying an entire row to another worsheet and it
> works fine
> > but I'd like to go one step further.  If I change the status of an
> employee
> > on the "Current User List" to "Prev" I want the macro to run so that
> the
> > record is copied to the "Prev User List automatically.
> >
> > Any help would be greatly apprciated.
> >
> 
> 
0
Coyote (9)
11/19/2004 9:29:05 PM
I think you have a problem.

You said you wanted it copied if you change the status.  You used the
worksheet_selectionchange event.  So if you click on another cell or move the
cursor, the code will run.

I think I'd use the worksheet_change event (you're typing the data, right???):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim SourceRange As Range
    Dim DestRange As Range
    Dim Lr As Long
    
    'one cell at a time!
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
    
    If LCase(Target.Value) <> "prev" Then Exit Sub
    
    Lr = LastRow(Sheets("Overall List - Prev Users")) + 1

    Set SourceRange = Target.EntireRow
    Set DestRange = Sheets("Overall List - Prev Users").Rows(Lr)
    
    Application.EnableEvents = False
    SourceRange.Copy DestRange
    SourceRange.EntireRow.Delete
    Application.EnableEvents = True

End Sub



Coyote wrote:
> 
> Thanks Frank,
> Look at my attached Code  In particular the Target.Address.  I want this
> routine exeuted anytime a change is made to column A regardless of which row
> is selected.  The code as it stands now will work but only if I select row 2
> column A.
> 
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Target.Address <> "$A2" Then Exit Sub
>  Dim sourceRange As Range
>     Dim destrange As Range
>     Dim Lr As Long
>     Lr = LastRow(Sheets("Overall List - Prev Users")) + 1
>     Set sourceRange = ActiveCell.EntireRow
>     Set destrange = Sheets("Overall List - Prev Users").Rows(Lr)
>     sourceRange.Copy destrange
>     sourceRange.EntireRow.Delete
> 
> End Sub
> Function LastRow(sh As Worksheet)
>     On Error Resume Next
>     LastRow = sh.Cells.Find(What:="*", _
>                             After:=sh.Range("A1"), _
>                             Lookat:=xlPart, _
>                             LookIn:=xlFormulas, _
>                             SearchOrder:=xlByRows, _
>                             SearchDirection:=xlPrevious, _
>                             MatchCase:=False).Row
>     On Error GoTo 0
> End Function
> 
> "Frank Kabel" wrote:
> 
> > Hi
> > then you need an event procedure. see:
> > http://www.cpearson.com/excel/events.htm
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> > "Coyote" <Coyote@discussions.microsoft.com> schrieb im Newsbeitrag
> > news:F1E0072A-048B-4D52-8D99-B6B6A22DEAF3@microsoft.com...
> > > Hi Everyone
> > >
> > > I have what seems to be a simple task but can't seem to figure it
> > out.  I
> > > have a excel file that has the tabs "Current User List" & "Prev User
> > List"
> > > On these sheets the first row is called "status"  which is either
> > "curr" or
> > > "prev" to indicate employee status.  I copied and modified the macro
> > posted
> > > on this site for copying an entire row to another worsheet and it
> > works fine
> > > but I'd like to go one step further.  If I change the status of an
> > employee
> > > on the "Current User List" to "Prev" I want the macro to run so that
> > the
> > > record is copied to the "Prev User List automatically.
> > >
> > > Any help would be greatly apprciated.
> > >
> >
> >

-- 

Dave Peterson
0
ec357201 (5290)
11/19/2004 10:43:50 PM
Dave,

Thanks this worked perfect.

"Dave Peterson" wrote:

> I think you have a problem.
> 
> You said you wanted it copied if you change the status.  You used the
> worksheet_selectionchange event.  So if you click on another cell or move the
> cursor, the code will run.
> 
> I think I'd use the worksheet_change event (you're typing the data, right???):
> 
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
>     Dim SourceRange As Range
>     Dim DestRange As Range
>     Dim Lr As Long
>     
>     'one cell at a time!
>     If Target.Cells.Count > 1 Then Exit Sub
>     
>     If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
>     
>     If LCase(Target.Value) <> "prev" Then Exit Sub
>     
>     Lr = LastRow(Sheets("Overall List - Prev Users")) + 1
> 
>     Set SourceRange = Target.EntireRow
>     Set DestRange = Sheets("Overall List - Prev Users").Rows(Lr)
>     
>     Application.EnableEvents = False
>     SourceRange.Copy DestRange
>     SourceRange.EntireRow.Delete
>     Application.EnableEvents = True
> 
> End Sub
> 
> 
> 
> Coyote wrote:
> > 
> > Thanks Frank,
> > Look at my attached Code  In particular the Target.Address.  I want this
> > routine exeuted anytime a change is made to column A regardless of which row
> > is selected.  The code as it stands now will work but only if I select row 2
> > column A.
> > 
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > If Target.Address <> "$A2" Then Exit Sub
> >  Dim sourceRange As Range
> >     Dim destrange As Range
> >     Dim Lr As Long
> >     Lr = LastRow(Sheets("Overall List - Prev Users")) + 1
> >     Set sourceRange = ActiveCell.EntireRow
> >     Set destrange = Sheets("Overall List - Prev Users").Rows(Lr)
> >     sourceRange.Copy destrange
> >     sourceRange.EntireRow.Delete
> > 
> > End Sub
> > Function LastRow(sh As Worksheet)
> >     On Error Resume Next
> >     LastRow = sh.Cells.Find(What:="*", _
> >                             After:=sh.Range("A1"), _
> >                             Lookat:=xlPart, _
> >                             LookIn:=xlFormulas, _
> >                             SearchOrder:=xlByRows, _
> >                             SearchDirection:=xlPrevious, _
> >                             MatchCase:=False).Row
> >     On Error GoTo 0
> > End Function
> > 
> > "Frank Kabel" wrote:
> > 
> > > Hi
> > > then you need an event procedure. see:
> > > http://www.cpearson.com/excel/events.htm
> > >
> > > --
> > > Regards
> > > Frank Kabel
> > > Frankfurt, Germany
> > >
> > > "Coyote" <Coyote@discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:F1E0072A-048B-4D52-8D99-B6B6A22DEAF3@microsoft.com...
> > > > Hi Everyone
> > > >
> > > > I have what seems to be a simple task but can't seem to figure it
> > > out.  I
> > > > have a excel file that has the tabs "Current User List" & "Prev User
> > > List"
> > > > On these sheets the first row is called "status"  which is either
> > > "curr" or
> > > > "prev" to indicate employee status.  I copied and modified the macro
> > > posted
> > > > on this site for copying an entire row to another worsheet and it
> > > works fine
> > > > but I'd like to go one step further.  If I change the status of an
> > > employee
> > > > on the "Current User List" to "Prev" I want the macro to run so that
> > > the
> > > > record is copied to the "Prev User List automatically.
> > > >
> > > > Any help would be greatly apprciated.
> > > >
> > >
> > >
> 
> -- 
> 
> Dave Peterson
> 
0
Coyote (9)
11/22/2004 2:59:05 PM
Reply:

Similar Artilces:

Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_...

Killer macro idea for business mailings
One of you programmers should write a macro to validate a city from zip code. I receive excel files from response cards and a lot of people will put some random letters for their city instead of the actual city name, or maybe they'll put the wrong abrieviation for their state. It would be awesome if all the zipcodes could validate the state field and change if necessary and the same for the city fields too. Okay, have at it :) Okay, well good idea I think anyways, but it turns out that my columns just weren't wide enough so it didn't display more then 2 letters. ...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Running Money 2003 in Vista
Hi I run this version perfectly satisfactorily in my Vista Ultimate desktop.with my normal log on. No fiddling was required to get it to start. It worked perfectly out of the box, just as in XP Referring to emails on earlier versions in the Newsgroup, my data is in a Documents folder on the D Drive well away from Vista, though it is secured. In this machine SSL2 is not enabled I have a newish laptop running Windows Home Premium with a similar configuration for all my data on the D drive. I recently tried to open Money for the first time since installation using a copy of the Money fil...

rounding up values
Has anyone done round up of values to the nearest dollar.For example I want to give a 10% of the price to my customers but if the result is other than .00 then I wanted to round up to the nearest dollar amount.My calculation using sql has been price * percent and then subtract the value from the price, then what do I need to do to roundit up??Thanks for your suggestion.Also I have a problem with my customers that I am extracting and the query does return all the values from 2004 and 2006 that are equal except for the price I have given them, how do I get only the latest ones in 2006 and not th...

TempVars unusable in field default value
Hello, I'm trying to use a temporary variable to keep track of which CSR is inputting data. I have a macro which prompts user for ID code, which is stored in the temp variable TempUser. On a form control default value property, I can use the expression [TempVars]![TempUser], which will populate that user's ID code into the control. However, I cannot use that same expression in the tables field default value property. If I try, when I save the changes to the table, I get the error message "Could not find the field 'TempVars]![TempUser'. " Any ideas why I ca...

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

How to make Run Integration work in GP 10
Can anyone tell me how to make the Run Integration tool work in GP 10. We just did our first client upgrade to GP 10 and the client has only 1 integration that they used to be able to run via the RUN INTEGRATION Tool. Any help would be greatly appreciated. -- Tammy Chavez I have the same probelm...did you get any answers to this? I have to go into Integration Manager to run it b/c Tools>Run Integration does nothing. I even checked the ini files to make sure the path is correct. "Tammy Chavez" wrote: > Can anyone tell me how to make the Run Integration tool work in...

VB Macro #2
I have a macro that is generating Excel files. When the macro runs, it saves two different tabs in the workbooks as values. It needs to test and wait until the formulas that are pulling amounts from another database are correctly populated with the amounts. Right now, I am getting N/A# instead of amounts for some, but not all, of the files. I think the problem is that the formulas have not completed pulling the amounts before the macro pastes that tab as zeros. (N/A# is what appears when the formulas have not completed calculating - once they have completed, the N/A# is replaced ...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

line chart with NA() values
12 month line chart, with some values being 0. I am using an if statement that turns any 0 values to #N/A so they do not show on the graph (which is what I want). My problem arises when the 0 values fall in the middle of my data. So for example: 1) data for all months (Jan-Dec), the line shows across all 12 months; 2) I have data for only 6 months (Jul-Dec), the line starts in Jul and ends in Dec (perfect); 3) When I have data from Jan-Mar, and Oct-Dec, the line connects between Mar and Oct. I want 2 distinct lines with no line where there is no data (#N/A). Any suggestions? -- gri...

Macros not performing correctly
I recorded a macro to place a vlookup formula in four cells that are side by side. When I run the macro it only performs the first task, it does not go on to do the other three. I used the tab key to go to the next cell while recording the macro, I don't know if that's why it's not working or if it's something else. The script looks like this: Sub EERates() ' ' EERates Macro ' Macro recorded 3/4/2005 by BCBSOK ' ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R3C1:R102C2,2,0)" Range("F15").Select ActiveCell.FormulaR1C...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

Multivalue with Null value SSRS 2005
I have a query to populate a multivalue parameter: SELECT distinct cast(AGRPYear.value as varchar(4)) + AGRPMonth.value 'ReportDate' FROM TPROJECT AS TPROJECT One of the values that is returned from this query is NULL. However, when I run the report, the NULL value does not show in the dropdown. I've also tried adding "select NULL as 'ReportDate' union" to the above query and the null value still doesn't show. As a result some of the records in my database have a null value for this field, they will never show up on my report. Any id...

Money 04 runs very slow
file size is 17,480 kb, about 5 years data. Machine is a P4 2.2 GHz, 512 mb ram. Money runs very slow, processing downloaded statements, switching screens, etc. I switched from Quicken about a year ago. That didn't run nearly this slow. Any ideas? Will trimming the file size help at all or much? Thanks Ideas? See http://umpmfaq.info/faqdb.php?q=4. Trimming? Most people haven't had great luck trying this through means like File|Archive--which, by the way, I'd discourage for any number of reasons. "Tom Orlando" <anonymous@discussions.microsoft.com> wrote i...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

Run time error 5 : HQClient
When I double click HQ Client I have message box "Run time error 5 : Invalid procedure call or argument" how should I do? I used RMS 2.0 -- TOY2TOY ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGr...

High Memory Usage after IS Maintenance Runs
I have an Exchange 5.5 server whose priv.edb file is about 2GBs. During normal usage the store.exe file is use about 70MBs of RAM. When the IS Maintenance runs at night the RAM usage shoots to 800MBS and does not come down after IS Maintenance is done. I have to restart the Information Store service to restore proper system operation. How do I fix this issue? Why do you think that proper operation is less than 800MB of RAM? By default, Exchange should take all of the system memory available and not 70MB (note that it should give it back when asked). That's normal behavior vs. w...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...