All Userform data to same cell on sheet

Annie62

New Member
Joined
Dec 14, 2018
Messages
4
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….

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
Thanks, in advance, for any help you can give me.
Annie62
 
Last edited by a moderator:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Something like this:

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
    ActiveCell.Value = Me.txtClient.Value & " " & _
                        Me.txtAddress.Value & " " & _
                        Me.cboTechs.Value & " " & _
                        DateValue(Me.txtDate.Value) & " " & _
                        Me.txtTime.Value & " " & _
                        Me.txtZones.Value & " " & _
                        Format(Now, "dd/mm/yyyy hh:nn:ss") & " " & _
                        Me.txtPhone.Value & " " & _
                        Me.cboServices.Value


'    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

Try and tell me.:biggrin:
 
Upvote 0
Well without providing all the other code it would look something like this.

ActiveCell.Value = TextBox1.Value & TextBox2.Value

Etc. Etc.
 
Upvote 0
I would do the entire script like this:
Assuming all you controls where you enter data are textboxes

Code:
Private Sub CommandButton2_Click()
'Modified  12/14/2018  3:23:22 PM  EST
Dim x As Long
Dim ans As String
x = 0
For Each xcontrol In Me.Controls
        If TypeName(xcontrol) = "TextBox" Then
            If xcontrol.Value = vbNullString Then
            xcontrol.BackColor = vbGreen
            x = x + 1
            Else:
            ans = ans & xcontrol.Value & " "
            
        End If
        End If
    Next xcontrol
If x > 0 Then MsgBox "You have not entered data into the controls colored Green": Exit Sub
ActiveCell.Value = ans
End Sub
 
Upvote 0
Thank You so much, this worked great with a few tweeks.
Any ideas where I could get a good tutorial, on VBA/Excel coding, online or book?
I'm really enjoying learning this.
Thanks again
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top