How to make a macro run once a dropdown list

Solved/Closed
funkyfreak - Mar 12, 2010 at 01:35 PM
 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

4 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Mar 12, 2010 at 05:24 PM
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
1
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).
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Mar 16, 2010 at 02:00 PM
Add line

Cell(thisrow, "D").select

just above

Application.EnableEvents = True
1
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
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!
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Mar 17, 2010 at 03:15 PM
Look into conditional formatting. That will resolve this issue
0