How to make a macro run once a dropdown list [Solved/Closed]

funkyfreak - Mar 12, 2010 at 01:35 PM - Latest reply:  Alarix
- May 4, 2012 at 11:20 AM
Hello,
I've been trying to figure out how to do this for 6 hours now!!
I have no idea how to make macros or use VB. I have been able to create a macro that will populate the cell in the A column with the date & the Cell in the B column with the time (with relative references so it will do it on whatever row the cursor is in) and have called it datetimeentry (its a module)
Sub datetimeentry()
'
' datetimeentry Macro
'
'
ActiveCell.Value = Date
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = Time
ActiveCell.Offset(0, 2).Range("A1").Select
End Sub


I have also created a dropdown list in column C to choose from. what I want to do is this:
I want to make the macro run once I have selected something from the dropdown list . this macro should run on the same row as the cell that I have picked from.
ie: if I select from the dropdown list in cell c1156 than I want the macro to run and insert the date in A1156 & time in B1156 then jump to cell D1156 for me to enter a value in.

Please help!! I have read and tried numerous options for different things and tried to adapt them to my needs but I have failed so far.

Thanks in advance!

funkyfreak
See more 

6 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 12, 2010 at 05:24 PM
1
Thank you
Well first let me say, failure is a very valid option :P

You need to define this function

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Let say the sheet that we are talking about is sheet1

So open the VBE and double click on sheet1 on the project explorer

Paste this routine and read the comments in the code about blank


Private Sub Worksheet_Change(ByVal Target As Range)

    'here 3 is column number 3 which is column C
    If (Target.Column <> 3) Then Exit Sub
    
    
    Application.EnableEvents = False
    
    thisrow = Target.Row

    If Target.Value = "" Then
        'now you can comment out the code if it does not make sense
        'but the idea is if the cell in column C has been cleared out,
        'corresponding date and time should be cleared out too
        Cells(thisrow, "A") = ""
        Cells(thisrow, "B") = ""
        
    Else
        Cells(thisrow, "A") = Date
        Cells(thisrow, "B") = Time
    
    End If
    
    Application.EnableEvents = True
    
End Sub

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1671 users this month

Great stuff! Thanks a million (hope it will not do much damage further down the track. i used it almost the same (wanted to copy formula of phoneline to value only, for people who left the company - as they would disappear from the master phone list after leaving the company).
Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 16, 2010 at 02:00 PM
1
Thank you
Add line

Cell(thisrow, "D").select

just above

Application.EnableEvents = True

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1671 users this month

0
Thank you
You ROCK man!! that worked great.. now is there any way to have the cursor move to the cell in 'D' after inputting the date and time?
0
Thank you
That worked great!
1 more thing I am being asked to do now is this: lets say I have data filled in a row (using the event created above), say row 2, if there is nothing in cell G2 then change the row background 'RED' . If data is entered into cell G2 then change background color back to white.

I am researching how to do this but am thinking it might be quicker just to ask. can you help with this too??
Thanks a bunch!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 17, 2010 at 03:15 PM
Look into conditional formatting. That will resolve this issue