Macro Problems

  • Thread starter Thread starter Don Guillett
  • Start date Start date
D

Don Guillett

First. You may only have ONE worksheet_change event per page. So, you will
have to combine.
Second. Be sure you are counting the rows in the area you want to sort.
Seems like a bit of a hassle to just add an item to the list and sort THAT
list unless you do this often.
 
Hello everyone.

I am using Excel 2000, and I am having a macro problem. On sheet 1 I have a
dropdown that I use data validation with and a macro. This list sorts
alphabetically, and I can type new names in when I need to.

It works fine, but I need to have two to three more dropdowns on the same
sheet. I tried to modify the macro, but it will not work properly.

I created a new list "VendorList" and modified the macro.(I think that is
where I messed up) here is a copy of the modified macro:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Sheet2")
If Target.Address = "$C$52" Then
If Application.WorksheetFunction.CountIf(ws.Range("NameList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Sheet2")
If Target.Address = "$I$4" Then
If Application.WorksheetFunction.CountIf(ws.Range("VendorList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("VendorList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub

The first part (the top part works fine)

When I tried this it gave me an error message and referred me to this
section at the top of the new part of the macro:

Private Sub Worksheet_Change(ByVal Target As Range)

Can someone please help me figure out how to correct this macro and then how
I would be able to add more dropdowns on the same sheet by using this same
macro.

Thank you
John Kitchens
 
Hello Don,

Thank you for your reply, but I don't know how to combine them. Can you
please help me with that?

Sincerely,
John Kitchens
 
You may have more than one if/end if so, just copy/paste the 2nd if/end if
after the 1st one.
 
glad to help.
OR try this (UN tested)

If Target.Address = "$C$52" Then myrng=range("Namelist")'one line
If Target.Address = "$I$4" Then myrng=range("VendorList")'one line
If Application.CountIf(.Range(myrng),Target.Value) Then Exit Sub 'one line

with ws
i = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range("A" & i).Value = Target.Value
.Range(myrng).Sort Key1:=.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

end with
 
Back
Top