Hi everybody!
finally completed my userform! I have got it to create a record on the next available row, search for a record and update it...
it has severely affected my brain cells and I have just stumbled upon my (hopefully) final hurdle. I hope somebody can help with.
The date format is MM/DD/YYYY and I need it to be DD/MM/YYYY. I have seen many posts saying to use an addin or something else that needs an activeX control but I can't use this. I have also seen bits of code to solve this but they all set a range. My range is an offset though from the last empty row. please see my code.
This is what I have to Create a new record
Code
Private Sub CommandButton1_Click()
Dim LastRow as Variant
Set LastRow = Sheet1.Range("C65536").End(xlUp) LastRow.Offset(1, 0).Value = ComboBox1.Text LastRow.Offset(1, 1).Value = ComboBox2.Text LastRow.Offset(1, 2).Value = ComboBox3.Text LastRow.Offset(1, 3).Value = ComboBox4.Text LastRow.Offset(1, 4).Value = TextBox2.Text ‘this textbox inputs the date
LastRow.Offset(1, 5).Value = TextBox3.Text ‘this textbox inputs the date
LastRow.Offset(1, 6).Value = TextBox4.Text LastRow.Offset(1, 7).Value = TextBox5.Text LastRow.Offset(1, 8).Value = TextBox6.Text LastRow.Offset(1, 9).Value = TextBox7.Text LastRow.Offset(1, 10).Value = TextBox8.Text LastRow.Offset(1, 11).Value = TextBox9.Text LastRow.Offset(1, 12).Value = TextBox10.Text LastRow.Offset(1, 13).Value = TextBox11.Text
MsgBox "Task Assigned"
response = MsgBox("Do you want to assign another task?",
_ vbYesNo)
If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
ComboBox1.Text = ""
ComboBox2.Text = ""
ComboBox3.Text = ""
ComboBox4.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If
End SubUpdate record
Code
Private Sub CommandButton4_Click()
Application.ScreenUpdating = False
If rng Is Nothing Then GoTo
skip For Each c In rng
If r = 0 Then c.Select
r = r - 1 Next cskip:
Set c = ActiveCell
' write amendments to database
c.Offset(0, 1).Value = Me.ComboBox1.Value
c.Offset(0, 2).Value = Me.ComboBox2.Value
c.Offset(0, 3).Value = Me.ComboBox3.Value
c.Offset(0, 4).Value = Me.ComboBox4.Value
c.Offset(0, 5).Value = Me.TextBox2.Value ‘this textbox inputs or changes the date
c.Offset(0, 6).Value = Me.TextBox3.Value ‘this textbox inputs or changes the date
c.Offset(0, 7).Value = Me.TextBox4.Value
c.Offset(0, 8).Value = Me.TextBox5.Value
c.Offset(0, 9).Value = Me.TextBox6.Value
c.Offset(0, 10).Value = Me.TextBox7.Value
c.Offset(0, 11).Value = Me.TextBox8.Value
c.Offset(0, 12).Value = Me.TextBox9.Value
c.Offset(0, 13).Value = Me.TextBox10.Value
c.Offset(0, 14).Value = Me.TextBox11.Value 'restore Form
With Me
.CommandButton1.Enabled = True .CommandButton2.Enabled = True .CommandButton3.Enabled = True .CommandButton4.Enabled = True
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
ComboBox1.Text = ""
ComboBox2.Text = ""
ComboBox3.Text = ""
ComboBox4.Text = ""
TextBox1.SetFocus
End With
If Sheet1.AutoFilterMode Then Sheet1.Range("A1").AutoFilter
Application.ScreenUpdating = True
On Error GoTo 0End Subsorry if this looks messy im writing this on my phone