Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Combo Box Change Event
Hello,
I only receive the follow error when I do a "Save As" for my excel document. Run-time error '1004': Select Method of Range Class Failed Why do I get this error when I use "Save As" and the debugger highlights the following line in the below code? Range("B9").Select Is there a way to use the sheet name instead of "ActiveSheet"? Private Sub cmbSort_Change() Sort_Data ActiveSheet, "1234", cmbSort.Value End Sub Private Sub Sort_Data(sht As Worksheet, pwd As String, val As String) ' Unprotect Worksheet sht.Unprotect Password:=pwd With sht If val = "Patient Name" Then ' Sort by Status by Patient Name With Worksheets("PatientList") .Range("PatientData").Sort Key1:=.Range("Status"), _ Order1:=xlAscending, Key2:=.Range("Patient_Name"), _ Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End With Range("B9").Select ElseIf val = "Discharge Date (No Status)" Then ' Sort by Discharge Date (No Status) With Worksheets("PatientList") .Range("PatientData").Sort Key1:=.Range("Discharge_Date"), _ Order1:=xlAscending, Key2:=.Range("Patient_Name"), _ Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End With Range("B9").Select Else ' Sort by Assigned Counselor Initials (No Status) With Worksheets("PatientList") .Range("PatientData").Sort Key1:=.Range("Couns_Assigned"), _ Order1:=xlAscending, Key2:=.Range("Patient_Name"), _ Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End With Range("B9").Select End If End With ' Protect Worksheet sht.Protect Password:=pwd, Scenarios:=True End Sub Thanks Ruan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Combo Box Change Event
Hi Ruan,
You are missing a . before your Range("B9"). Try specifying which sheet Range("B9") is on. If something is going to happen to other sheets (like sorting it) you should explicitly activate before selecting. That way, you don't have to worry about whether some worksheet method is going to activate a sheet or not. e.g. (Tidied your code a bit too. Nesting With..end With statements always makes me feel a bit quesy) Private Sub Sort_Data(sht As Worksheet, pwd As String, val As String) ' Unprotect Worksheet sht.Unprotect Password:=pwd With Worksheets("PatientList") If val = "Patient Name" Then ' Sort by Status by Patient Name .Range("PatientData").Sort Key1:=.Range("Status"), _ Order1:=xlAscending, Key2:=.Range("Patient_Name"), _ Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom ElseIf val = "Discharge Date (No Status)" Then ' Sort by Discharge Date (No Status) .Range("PatientData").Sort Key1:=.Range("Discharge_Date"), _ Order1:=xlAscending, Key2:=.Range("Patient_Name"), _ Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Else ' Sort by Assigned Counselor Initials (No Status) .Range("PatientData").Sort Key1:=.Range("Couns_Assigned"), _ Order1:=xlAscending, Key2:=.Range("Patient_Name"), _ Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End If End With 'Worksheets("PatientList") With sht .Activate 'just to be SURE it is active .Range("B9").Select ' Protect Worksheet .Protect Password:=pwd, Scenarios:=True End With End Sub regards Paul "Ruan" wrote in message ... Hello, I only receive the follow error when I do a "Save As" for my excel document. Run-time error '1004': Select Method of Range Class Failed Why do I get this error when I use "Save As" and the debugger highlights the following line in the below code? Range("B9").Select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change event | Excel Discussion (Misc queries) | |||
Worksheet Change event | Excel Discussion (Misc queries) | |||
Change event? | Excel Discussion (Misc queries) | |||
Change of Row event | Excel Discussion (Misc queries) | |||
change event/after update event?? | Excel Programming |