Code to run macros from drop downlist, but undo if another macro is run.

E

Elnicko

New Member
Joined
Aug 17, 2011
Messages
31
Hi all I started using vba yesterday and im trying to edit this code.

I have 1000 cells with dropdown lists in them in colum I, this needs to be applied to all of them. (their all the same)

And If a different value is selected I need it to undo the last Marco.

Is this possible

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$9" Then
If Target = "Paul" Then
MacroA
ElseIf Target = "Rachel" Then
MacroB
ElseIf Target = "Julija" Then
MacroC
ElseIf Target = "Sue" Then
MacroD

End If
End If
End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
This seems to work for mulitple drop downlists
But is there an easier way?

As doing this 1000 time seems crazy

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$9" Then
If Target = "Paul" Then
MacroA
ElseIf Target = "Rachel" Then
MacroB
ElseIf Target = "Julija" Then
MacroC
ElseIf Target = "Sue" Then
MacroD
End If
End If
If Target.Address = "$I$12" Then
If Target = "Paul" Then
MacroA
ElseIf Target = "Rachel" Then
MacroB
ElseIf Target = "Julija" Then
MacroC
ElseIf Target = "Sue" Then
MacroD

End If
End If

If Target.Address = "$I$15" Then
If Target = "Paul" Then
MacroA
ElseIf Target = "Rachel" Then
MacroB
ElseIf Target = "Julija" Then
MacroC
ElseIf Target = "Sue" Then
MacroD

End If
End If
End Sub
 
Upvote 0
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 And Target.Column = 9 Then
        If Target.Row >= 9 And Target.Row <= 1000 And Target.Row / 3 = Int(Target.Row / 3) Then
            Select Case Target.Value
                Case "Paul": MacroA
                Case "Julija": MacroB
                Case "Sue": MacroC
            End Select
        End If
    End If
End Sub

I don't know what the macros "do" so I can't suggest how to undo them.
 
Upvote 0
The macros of which only the first two work at the moment, pull data from a master job register and place it into the relevant work sheet. here is the code




Code: 
Sub MacroA()
    
    Dim lNextRow As Long
    
    With ThisWorkbook.Worksheets(ActiveCell.Text)
        lNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("A" & lNextRow).Value = ActiveCell.Offset(, -1).Value
        .Range("E" & lNextRow).Value = ActiveCell.Offset(, -3).Value & ActiveCell.Offset(, -2).Value
        .Range("F" & lNextRow).Value = ActiveCell.Offset(, 1).Value
    End With

End Sub
Sub MacroB()
 
 Dim lNextRow As Long
    
    With ThisWorkbook.Worksheets(ActiveCell.Text)
        lNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("A" & lNextRow).Value = ActiveCell.Offset(, -1).Value
        .Range("E" & lNextRow).Value = ActiveCell.Offset(, -3).Value & ActiveCell.Offset(, -2).Value
        .Range("F" & lNextRow).Value = ActiveCell.Offset(, 1).Value
    End With
End Sub
 
Upvote 0
This is my full list of macros but only MacroA AND MacroB work?

Code: 
Sub MacroA()
    
    Dim lNextRow As Long
    
    With ThisWorkbook.Worksheets(ActiveCell.Text)
        lNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("A" & lNextRow).Value = ActiveCell.Offset(, -1).Value
        .Range("E" & lNextRow).Value = ActiveCell.Offset(, -3).Value & ActiveCell.Offset(, -2).Value
        .Range("F" & lNextRow).Value = ActiveCell.Offset(, 1).Value
    End With

End Sub
Sub MacroB()
 
 Dim lNextRow As Long
    
    With ThisWorkbook.Worksheets(ActiveCell.Text)
        lNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("A" & lNextRow).Value = ActiveCell.Offset(, -1).Value
        .Range("E" & lNextRow).Value = ActiveCell.Offset(, -3).Value & ActiveCell.Offset(, -2).Value
        .Range("F" & lNextRow).Value = ActiveCell.Offset(, 1).Value
    End With
End Sub
Sub MacroC()
 Dim lNextRow As Long
    
    With ThisWorkbook.Worksheets(ActiveCell.Text)
        lNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("A" & lNextRow).Value = ActiveCell.Offset(, -1).Value
        .Range("E" & lNextRow).Value = ActiveCell.Offset(, -3).Value & ActiveCell.Offset(, -2).Value
        .Range("F" & lNextRow).Value = ActiveCell.Offset(, 1).Value
    End With
End Sub
Sub MacroD()
 Dim lNextRow As Long
    
    With ThisWorkbook.Worksheets(ActiveCell.Text)
        lNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("A" & lNextRow).Value = ActiveCell.Offset(, -1).Value
        .Range("E" & lNextRow).Value = ActiveCell.Offset(, -3).Value & ActiveCell.Offset(, -2).Value
        .Range("F" & lNextRow).Value = ActiveCell.Offset(, 1).Value
    End With
End Sub
Sub MacroE()
 Dim lNextRow As Long
    
    With ThisWorkbook.Worksheets(ActiveCell.Text)
        lNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("A" & lNextRow).Value = ActiveCell.Offset(, -1).Value
        .Range("E" & lNextRow).Value = ActiveCell.Offset(, -3).Value & ActiveCell.Offset(, -2).Value
        .Range("F" & lNextRow).Value = ActiveCell.Offset(, 1).Value
    End With
End Sub



This is your code which I have adjusted thanks again!!


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 And Target.Column = 9 Then
        If Target.Row >= 9 And Target.Row <= 1000 And Target.Row / 3 = Int(Target.Row / 3) Then
            Select Case Target.Value
                Case "Paul": MacroA
                Case "Rachel": MacroB
                Case "Julija": MacroC
                Case "Sue": MacroD
                Case "James": MacroE
            End Select
        End If
    End If
End Sub
 
Upvote 0
Your five macros A to E appear to be identical or am I missing something? Why are you calling 5 different macros that do exactly the same thing?
 
Upvote 0
No your not missing anything.. Like I said yesterday was the first time I opened vba and I'm not the brightest spark.

I just thought I need a macro for each sheet.
Just changed the code and it works.

The only problem I have now is if I run the macro twice on the same row it populates again as apose to changing the values that are there.

And I need to find code to create a link in a cell in the Master job register to the row reference in the sales sheets.

Thanks for all your help these forums are great!!

I didn't understand this peice of the code properly
Code: 
With ThisWorkbook.Worksheets(ActiveCell.Text)
 
Last edited:
Upvote 0
When you make a change to one of the drop downs, the code below logs the data to the selected sheet. It also logs in column Z the cell address of the drop down that added that row to the sheet. If you later change that drop-down, the code searches for that drop-down's address in column Z on each sheet and deletes that row. Then logs the data to the newly selected sheet.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lNextRow As Long, ws As Worksheet, Found As Range
    
    If Target.Count = 1 And Target.Column = 9 Then
        If Target.Row >= 9 And Target.Row <= 1000 And Target.Row / 3 = Int(Target.Row / 3) Then
            If Target.Value <> "" Then
                'Test if Worksheet from drop down list exists
                On Error Resume Next
                    Set ws = ThisWorkbook.Worksheets(Target.Text)
                On Error GoTo 0
                If ws Is Nothing Then
                    MsgBox "Cannot locate a worksheet named " & Target.Text, vbExclamation, "Sheet Does Not Exist"
                Else
                    'Undo
                    For Each ws In Sheets(Array("Paul", "Rachel", "Julija", "Sue", "James"))
                        Set Found = ws.Columns("Z").Find(Target.Address(0, 0), , xlValues, xlWhole)
                        If Not Found Is Nothing Then
                            Found.EntireRow.Delete
                            Exit For
                        End If
                    Next ws
                    ' Log to selected sheet
                    With ThisWorkbook.Worksheets(Target.Text)
                        lNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                        .Range("A" & lNextRow).Value = Target.Offset(, -1).Value
                        .Range("E" & lNextRow).Value = Target.Offset(, -3).Value & Target.Offset(, -2).Value
                        .Range("F" & lNextRow).Value = Target.Offset(, 1).Value
                        .Range("Z" & lNextRow).Value = Target.Address(0, 0)
                    End With
                End If
            End If
        End If
    End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Select the Name in the Data Validation list on a cell value then the same name run the sub macro
Replies
9
Views
450
kpmsivaprakasam2003
K
J
  • Question Question
Run macro when cell is altered
Replies
1
Views
215
Joe4
Joe4
M
  • Question Question
Not running code
Replies
0
Views
212
MagicCarpet
M
M
  • Question Question
Prevent worksheet change from triggering when similar macro is executed?
Replies
1
Views
141
AlphaFrog
AlphaFrog
J
  • Question Question
Multiple conditional formatting
Replies
6
Views
283
StephenCrump
S

Forum statistics

Threads
1,224,566
Messages
6,179,555
Members
452,928
Latest member
101blockchains

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top