how to make a macro run once a dropdown listSolved/Closed

Question

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

rizvisa1 · Answer

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

funkyfreak · Answer

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?

rizvisa1 · Answer

Add line

Cell(thisrow, "D").select

just above 

Application.EnableEvents = True

funkyfreak · Answer

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!

How to make a macro run once a dropdown list

4 responses

Similar discussions

Subscribe To Our Newsletter!