I’m new to VBA/Excel Coding and I am trying to do the following…
When I click on a cell on the CALENDAR sheet the userform opens automatically and I fill it out.
What I want to do now is, when I click OK on the userform, I want all of the data to go into the same single cell I selected to open the userform. Is this possible?
Example: If I select C6 on CALENDAR sheet, I want all data to go to C6 when I click OK on userform.
Right now I have all of the data going to SHEET 2 in individual columns, which I don’t want.
Here is the code I have so far….
Thanks, in advance, for any help you can give me.
Annie62
When I click on a cell on the CALENDAR sheet the userform opens automatically and I fill it out.
What I want to do now is, when I click OK on the userform, I want all of the data to go into the same single cell I selected to open the userform. Is this possible?
Example: If I select C6 on CALENDAR sheet, I want all data to go to C6 when I click OK on userform.
Right now I have all of the data going to SHEET 2 in individual columns, which I don’t want.
Here is the code I have so far….
Code:
Private Sub cmdOK_Click()
Dim RowCount As Long
Dim ctl As control
' Check user input
If Me.txtClient.Value = "" Then
MsgBox "Please enter a Client."
Me.txtClient.SetFocus
Exit Sub
End If
If Me.txtAddress.Value = "" Then
MsgBox "Please enter an Address."
Me.txtClient.SetFocus
Exit Sub
End If
If Me.txtPhone.Value = "" Then
MsgBox "Please enter Phone No."
Me.txtClient.SetFocus
Exit Sub
End If
If Me.cboTechs.Value = "" Then
MsgBox "Please choose a Tech."
Me.txtClient.SetFocus
Exit Sub
End If
If Me.cboServices.Value = "" Then
MsgBox "Please choose a Service."
Me.txtClient.SetFocus
Exit Sub
End If
If Me.txtDate.Value = "" Then
MsgBox "Please enter a Date."
Me.txtClient.SetFocus
Exit Sub
End If
If Me.txtTime.Value = "" Then
MsgBox "Please enter a Time."
Me.txtClient.SetFocus
Exit Sub
End If
If Me.txtZonesValue = "" Then
MsgBox "Please enter Zones."
Me.txtClient.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtDate.Value) Then
MsgBox "The Date box must contain a date."
Me.txtDate.SetFocus
Exit Sub
End If
' Write data to worksheet
RowCount = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("A1")
.Offset(RowCount, 0).Value = Me.txtClient.Value
.Offset(RowCount, 1).Value = Me.txtAddress.Value
.Offset(RowCount, 2).Value = Me.cboTechs.Value
.Offset(RowCount, 3).Value = DateValue(Me.txtDate.Value)
.Offset(RowCount, 4).Value = Me.txtTime.Value
.Offset(RowCount, 5).Value = Me.txtZones.Value
.Offset(RowCount, 6).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
.Offset(RowCount, 7).Value = Me.txtPhone.Value
.Offset(RowCount, 8).Value = Me.cboServices.Value
End If
End With
' Clear the form
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
End Sub
Annie62
Last edited by a moderator: