Execute macro on drop-down list change

[SpectreHUN]

So I have a Sheet, where I want to show only the data which is relevant based on the user's selection in the drop-down list (G3). My code so-far:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G3")) Is Nothing Then
Application.Run "HideRows"
End If
End Sub
Sub HideRows()
On Error Resume Next
LTRW = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To LTRW
    If Cells(i, 7).Value = "Import" Then
    For j = 60 To 82
        Cells(j, 20).EntireRow.Hidden = True
      
    Next j
    End If
    If Cells(i, 7).Value = "Közösségen belüli beszerzés" Then
    For j = 60 To 82
        Cells(j, 20).EntireRow.Hidden = False
      
    Next j
    End If
Next i
End Sub

It works, but it's not automatic. I have to manually run the macro every time there is a change in G3. Any help would be appreciated!

 

[Alan_P]

Try change the first line to

Private Sub Worksheet_[COLOR=#ff0000]Selection[/COLOR]Change(ByVal Target As Range)

 

[SpectreHUN]

Does the same thing, still not automatic unfortunately.

 

[Alan_P]

The only way I could get it to work was to individually reference the row and column...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 7 Then

 

[Alan_P]

Ah I see why you don'y want the "Selection" part now, runs if you just click on the cell rather than change it...

 

[SpectreHUN]

Ok, so I've simplified it. I still have to run it manually though. :S

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Row = 3 And Target.Column = 7 Then
Application.Run "HideRows"
End If
End Sub
Sub HideRows()
On Error Resume Next

    If Cells(3, 7).Value = "Import" Then
    For j = 60 To 82
        Cells(j, 20).EntireRow.Hidden = True
    Next j
    End If
    
    If Cells(3, 7).Value = "Közösségen belüli beszerzés" Then
    For j = 60 To 82
        Cells(j, 20).EntireRow.Hidden = False
    Next j
    End If
    
End Sub

 

[Alan_P]

Bit of a head twister this one
I think it's because the HideRows macro needs to be in a module
so...

on the sheet you want this code to work on, right click the tab and select view code... then paste this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 7 Then
Application.Run "HideRows"
End If
End Sub

Then in the VBA screen click "insert" at the top and select "Module"

and paste this code in there

Sub HideRows()
On Error Resume Next
 
    If Cells(3, 7).Value = "Import" Then
    For j = 60 To 82
        Cells(j, 20).EntireRow.Hidden = True
    Next j
    End If
   
    If Cells(3, 7).Value = "Közösségen belüli beszerzés" Then
    For j = 60 To 82
        Cells(j, 20).EntireRow.Hidden = False
    Next j
    End If
   
End Sub

So the way you have re-written your code this is what should happen:

If cell G3 is changed to "Import", rows 60-82 will be hidden
If cell G3 is changed to "Közösségen belüli beszerzés" rows 60-82 will not be hidden

Is that outcome you're looking for?

Cheers,
Alan.

 

[SpectreHUN]

Yep. And thanks so far! This works also, but again, same problem. The G3 cell changes to either "Import" or "Közösségen belüli beszerzés" ( Intra-community acquisition) based on which supplier is selected from a drop-down list. Now when I run the macro, it gives the desired result, but when I change the supplier, thus changing what is displayed in the G3 cell, nothing happens. I have to manually press Run again, for it to work.

 

[Alan_P]

Ahhhhh.... Your original post said that the dropdown menu was in G3!

so delete all the other codes and just put this in the sheet section

Private Sub Worksheet_Change(ByVal Target As Range)


If Cells(3, 7).Value = "Import" Then
Rows("60:82").Hidden = True
End If


If Cells(3, 7).Value = "Közösségen belüli beszerzés" Then
Rows("60:82").Hidden = False
End If


End Sub

 

[SpectreHUN]

Sorry about the confusion.
This one requires a Macro to execute, because when I run your code it wants to create a new Macro.