Re: Changing from Worksheet(Sheet1) to Activesheet
Hi,
Try using this code for your OK button:
Code
Private Sub cmdOK_Click()
Dim ctl As Control
Dim RowCount As Long
RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
Range("A5").End(xlDown).Select
' With Worksheets("Sheet1").Range("A6")
ActiveCell.Offset(1, 0).Value = Me.txtName1.Value
ActiveCell.Offset(1, 1).Value = Me.txtOwner1.Value
ActiveCell.Offset(1, 2).Value = Me.txtAddress1.Value
ActiveCell.Offset(1, 3).Value = Me.txtAccountNumber1.Value
ActiveCell.Offset(1, 4).Value = Me.txtTelephoneNumber1.Value
ActiveCell.Offset(1, 5).Value = Me.txtMeterNumber1.Value
ActiveCell.Offset(1, 6).Value = Me.txtC3IID1.Value
ActiveCell.Offset(1, 7).Value = Me.txtMeterManufacturer1.Value
ActiveCell.Offset(1, 8).Value = Me.txtMeterSize1.Value
ActiveCell.Offset(1, 9).Value = Me.txtServiceSize1.Value
ActiveCell.Offset(1, 10).Value = Me.txtCurbstopLocation1.Value
ActiveCell.Offset(1, 11).Value = Me.txtMeterLocation1.Value
ActiveCell.Offset(1, 12).Value = Me.txtComments1.Value
ActiveCell.Offset(1, 13).Value = Me.txthome1.Value
ActiveCell.Offset(1, 14).Value = Me.txtmobile1.Value
ActiveCell.Offset(1, 15).Value = Me.txtg4smobile1.Value
ActiveCell.Offset(1, 16).Value = Me.txtimei1.Value
ActiveCell.Offset(1, 17).Value = Me.txtvehicletype1.Value
ActiveCell.Offset(1, 18).Value = Me.txtvehiclemake1.Value
ActiveCell.Offset(1, 19).Value = Me.txtvehiclereg1.Value
ActiveCell.Offset(1, 20).Value = Me.txtkey1.Value
ActiveCell.Offset(1, 21).Value = Me.txtexpiry1.Value
ActiveCell.Offset(1, 22).Value = Me.txtnetwork1.Value
ActiveCell.Offset(1, 23).Value = Me.txtj421.Value
ActiveCell.Offset(1, 24).Value = Me.txthhc1.Value
ActiveCell.Offset(1, 25).Value = Me.txtmodemmake1.Value
ActiveCell.Offset(1, 26).Value = Me.txtmodemserial1.Value
ActiveCell.Offset(1, 27).Value = Me.txtfs31.Value
ActiveCell.Offset(1, 28).Value = Me.txtgist11.Value
ActiveCell.Offset(1, 29).Value = Me.txtgist21.Value
ActiveCell.Offset(1, 30).Value = Me.txtgist31.Value
ActiveCell.Offset(1, 31).Value = Me.txtgist41.Value
ActiveCell.Offset(1, 32).Value = Me.txtrefresher1.Value
ActiveCell.Offset(1, 33).Value = Me.txtleave1.Value
' End With
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
'
' alphabetize Macro
'
'
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A6:A500") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A5:ai211")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Display More
PS. Enter manually the first value at row 6 for the code to work. You just need to enter the first value, then all of the following can be entered using the UserForm.