My code adds data to worksheet (“Members”) without selecting the worksheet.
In order to sort the worksheet I have to select the ws and then select the range. Otherwise, the code sorts the active worksheet.
How can I modify my code to sort worksheet ("Members”) without selecting it?
Additionally, worksheet ("Members") will eventually exceed the selection range (JZ500). Can this be revised to make the range dynamic?
Thanks for looking!
In order to sort the worksheet I have to select the ws and then select the range. Otherwise, the code sorts the active worksheet.
How can I modify my code to sort worksheet ("Members”) without selecting it?
Additionally, worksheet ("Members") will eventually exceed the selection range (JZ500). Can this be revised to make the range dynamic?
Thanks for looking!
Rich (BB code):
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("Members").Unprotect
Dim MemberName As String
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Members")
MemberName = Me.TextBox2.Value & " " & Me.TextBox1.Value
Set CLoc = ws.Columns("C:C").Find(What:=MemberName, after:=ws.Cells(1, 3), LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
If CLoc Is Nothing Then
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
Else
iRow = CLoc.Row
End If
With ws
ws.Cells(iRow, 1) = StrConv(TextBox1.Text, vbProperCase)
ws.Cells(iRow, 2) = StrConv(TextBox2.Text, vbProperCase)
ws.Cells(iRow, 3) = StrConv(TextBox2.Text, vbProperCase) & " " & StrConv(TextBox1.Text, vbProperCase)
ws.Cells(iRow, 4) = TextBox7.Value & Me.TextBox8.Value & Me.TextBox9.Value
ws.Cells(iRow, 5) = StrConv(TextBox10.Text, vbLowerCase)
ws.Cells(iRow, 7) = StrConv(TextBox3.Text, vbProperCase)
ws.Cells(iRow, 10) = Me.TextBox6.Value
ws.Cells(iRow, 13) = Me.TextBox14.Value
ws.Cells(iRow, 8) = StrConv(ComboBox2.Text, vbProperCase)
ws.Cells(iRow, 9) = StrConv(ComboBox3.Text, vbUpperCase)
ws.Select
[A2:JZ500].Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("A2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormalHeader
[A2].Select
‘More code follows
End Sub