One Command Button with two functions

advent

New Member
Joined
Jun 20, 2011
Messages
25
I have a user form with two separate command buttons to do two different tasks when clicked, that I am trying to combine into just the single command button.
One command button is coded to take the contents of a range of textbox values within the form and enter them when clicked into a dedicated workbook data table.
The user form is a client data sheet and the button is used to enter the details of a new client.
The second button, when clicked will crate a new client folder taking its name from a specific textbox value (client name)
The coding for the first button is quite lengthy as follows:

VBA Code:
Private Sub Commandbutton10_Click()
Dim rw As Integer
Dim ws As Worksheet
Set ws = Worksheets("Client_Database")
rw = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

If Trim(Me.TextBox4.Value) = "" Then
MsgBox "Please enter the Holders Surname field!"
Exit Sub
End If
If Trim(Me.TextBox6.Value) = "" Then
MsgBox "Please enter the Client Account Number field!"
Exit Sub
End If
If Trim(Me.TextBox3.Value) = "" Then
MsgBox "Please enter the card status field!"
Exit Sub
End If
If Trim(Me.TextBox4.Value) = "" Then
MsgBox "Please enter the expiry date field!"
Exit Sub
End If
If Trim(Me.TextBox11.Value) = "" Then
MsgBox "Please enter Company Name field!"
Exit Sub
End If
If Trim(Me.TextBox14.Value) = "" Then
MsgBox "Please enter a Post Code field!"
Exit Sub
End If
If Trim(Me.TextBox21.Value) = "" Then
MsgBox "Please enter Clients Unique Password field!"
Exit Sub
End If


ws.Cells(rw, 1).Value = Me.TextBox6.Value
ws.Cells(rw, 2).Value = Me.TextBox3.Value
ws.Cells(rw, 3).Value = Me.TextBox4.Value
ws.Cells(rw, 4).Value = Me.TextBox11.Value
ws.Cells(rw, 5).Value = Me.TextBox12.Value
ws.Cells(rw, 7).Value = Me.TextBox21.Value
ws.Cells(rw, 8).Value = Me.TextBox14.Value
ws.Cells(rw, 9).Value = Me.TextBox17.Value
ws.Cells(rw, 10).Value = Me.TextBox18.Value
ws.Cells(rw, 13).Value = Me.TextBox20.Value
ws.Cells(rw, 19).Value = Me.TextBox30.Value
Me.TextBox1.SetFocus

TextBox6.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox11.Value = ""
TextBox12.Value = ""
TextBox21.Value = ""
TextBox14.Value = ""
TextBox17.Value = ""
TextBox18.Value = ""
TextBox20.Value = ""
TextBox19.Value = ""

If (Me.TextBox6.Value) = "" Then
MsgBox "Your New Client Has Been Added, thank you"
Exit Sub
End If

End Sub

Sub resetForm()
TextBox6.Value = ""
TextBox3.Value = ""
'TextBox4.Value = ""
TextBox11.Value = ""
TextBox12.Value = ""
TextBox21.Value = ""
TextBox14.Value = ""
TextBox17.Value = ""
TextBox18.Value = ""
TextBox20.Value = ""
TextBox19.Value = ""
frmMenu.TextBox6.SetFocus

End Sub
End Sub

NOTE: I have stopped TextBox4 from resetting as I have found I need it to remain displayed so when clicking the second button, the code will have the textBox value to give the file its name.

The second button code is a lot simpler:
VBA Code:
Private Sub CommandButton14_Click()
  
    Dim Newfolder As String, path As String
  
    Newfolder = Trim(TextBox4.Text)
    path = "C:\Users\Owner\OneDrive\Desktop\Client Records\CSS_Desktop_System\CSS_Admin\Customer_Records\" & Newfolder
  
    With CreateObject("Scripting.FileSystemObject")
        If Not .FolderExists(path) Then .CreateFolder path
        MsgBox "Your New Folder has been Created for you"
      
      
        End With
      
End Sub

CommanmdButton10 relates to the first button coding, and the second coding relates to commandButton14.
My goal is to just have the one CommandButton that when clicked it will create the client record and a client folder within the company Admin Folder.
Any help would be greatly appreciated

Thank you
Advent
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Tis will perform your original action of Button10 first and the the action of Button 4:

VBA Code:
Private Sub Commandbutton10_Click()
Dim rw As Integer
Dim ws As Worksheet
Set ws = Worksheets("Client_Database")
rw = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

If Trim(Me.TextBox4.Value) = "" Then
MsgBox "Please enter the Holders Surname field!"
Exit Sub
End If
If Trim(Me.TextBox6.Value) = "" Then
MsgBox "Please enter the Client Account Number field!"
Exit Sub
End If
If Trim(Me.TextBox3.Value) = "" Then
MsgBox "Please enter the card status field!"
Exit Sub
End If
If Trim(Me.TextBox4.Value) = "" Then
MsgBox "Please enter the expiry date field!"
Exit Sub
End If
If Trim(Me.TextBox11.Value) = "" Then
MsgBox "Please enter Company Name field!"
Exit Sub
End If
If Trim(Me.TextBox14.Value) = "" Then
MsgBox "Please enter a Post Code field!"
Exit Sub
End If
If Trim(Me.TextBox21.Value) = "" Then
MsgBox "Please enter Clients Unique Password field!"
Exit Sub
End If


ws.Cells(rw, 1).Value = Me.TextBox6.Value
ws.Cells(rw, 2).Value = Me.TextBox3.Value
ws.Cells(rw, 3).Value = Me.TextBox4.Value
ws.Cells(rw, 4).Value = Me.TextBox11.Value
ws.Cells(rw, 5).Value = Me.TextBox12.Value
ws.Cells(rw, 7).Value = Me.TextBox21.Value
ws.Cells(rw, 8).Value = Me.TextBox14.Value
ws.Cells(rw, 9).Value = Me.TextBox17.Value
ws.Cells(rw, 10).Value = Me.TextBox18.Value
ws.Cells(rw, 13).Value = Me.TextBox20.Value
ws.Cells(rw, 19).Value = Me.TextBox30.Value
Me.TextBox1.SetFocus

MsgBox "Your New Client Has Been Added, thank you"

Dim Newfolder As String, path As String
Newfolder = Trim(TextBox4.Text)
path = "C:\Users\Owner\OneDrive\Desktop\Client Records\CSS_Desktop_System\CSS_Admin\Customer_Records\" & Newfolder
 
 With CreateObject("Scripting.FileSystemObject")
 If Not .FolderExists(path) Then .CreateFolder path
 MsgBox "Your New Folder has been Created for you"
End With

TextBox6.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox11.Value = ""
TextBox12.Value = ""
TextBox21.Value = ""
TextBox14.Value = ""
TextBox17.Value = ""
TextBox18.Value = ""
TextBox20.Value = ""
TextBox19.Value = ""

End Sub
 
Upvote 0
Solution
Tis will perform your original action of Button10 first and the the action of Button 4:

VBA Code:
Private Sub Commandbutton10_Click()
Dim rw As Integer
Dim ws As Worksheet
Set ws = Worksheets("Client_Database")
rw = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

If Trim(Me.TextBox4.Value) = "" Then
MsgBox "Please enter the Holders Surname field!"
Exit Sub
End If
If Trim(Me.TextBox6.Value) = "" Then
MsgBox "Please enter the Client Account Number field!"
Exit Sub
End If
If Trim(Me.TextBox3.Value) = "" Then
MsgBox "Please enter the card status field!"
Exit Sub
End If
If Trim(Me.TextBox4.Value) = "" Then
MsgBox "Please enter the expiry date field!"
Exit Sub
End If
If Trim(Me.TextBox11.Value) = "" Then
MsgBox "Please enter Company Name field!"
Exit Sub
End If
If Trim(Me.TextBox14.Value) = "" Then
MsgBox "Please enter a Post Code field!"
Exit Sub
End If
If Trim(Me.TextBox21.Value) = "" Then
MsgBox "Please enter Clients Unique Password field!"
Exit Sub
End If


ws.Cells(rw, 1).Value = Me.TextBox6.Value
ws.Cells(rw, 2).Value = Me.TextBox3.Value
ws.Cells(rw, 3).Value = Me.TextBox4.Value
ws.Cells(rw, 4).Value = Me.TextBox11.Value
ws.Cells(rw, 5).Value = Me.TextBox12.Value
ws.Cells(rw, 7).Value = Me.TextBox21.Value
ws.Cells(rw, 8).Value = Me.TextBox14.Value
ws.Cells(rw, 9).Value = Me.TextBox17.Value
ws.Cells(rw, 10).Value = Me.TextBox18.Value
ws.Cells(rw, 13).Value = Me.TextBox20.Value
ws.Cells(rw, 19).Value = Me.TextBox30.Value
Me.TextBox1.SetFocus

MsgBox "Your New Client Has Been Added, thank you"

Dim Newfolder As String, path As String
Newfolder = Trim(TextBox4.Text)
path = "C:\Users\Owner\OneDrive\Desktop\Client Records\CSS_Desktop_System\CSS_Admin\Customer_Records\" & Newfolder
 
 With CreateObject("Scripting.FileSystemObject")
 If Not .FolderExists(path) Then .CreateFolder path
 MsgBox "Your New Folder has been Created for you"
End With

TextBox6.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox11.Value = ""
TextBox12.Value = ""
TextBox21.Value = ""
TextBox14.Value = ""
TextBox17.Value = ""
TextBox18.Value = ""
TextBox20.Value = ""
TextBox19.Value = ""

End Sub[/CODE
[/QUOTE]
 
Upvote 0
Tis will perform your original action of Button10 first and the the action of Button 4:

VBA Code:
Private Sub Commandbutton10_Click()
Dim rw As Integer
Dim ws As Worksheet
Set ws = Worksheets("Client_Database")
rw = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

If Trim(Me.TextBox4.Value) = "" Then
MsgBox "Please enter the Holders Surname field!"
Exit Sub
End If
If Trim(Me.TextBox6.Value) = "" Then
MsgBox "Please enter the Client Account Number field!"
Exit Sub
End If
If Trim(Me.TextBox3.Value) = "" Then
MsgBox "Please enter the card status field!"
Exit Sub
End If
If Trim(Me.TextBox4.Value) = "" Then
MsgBox "Please enter the expiry date field!"
Exit Sub
End If
If Trim(Me.TextBox11.Value) = "" Then
MsgBox "Please enter Company Name field!"
Exit Sub
End If
If Trim(Me.TextBox14.Value) = "" Then
MsgBox "Please enter a Post Code field!"
Exit Sub
End If
If Trim(Me.TextBox21.Value) = "" Then
MsgBox "Please enter Clients Unique Password field!"
Exit Sub
End If


ws.Cells(rw, 1).Value = Me.TextBox6.Value
ws.Cells(rw, 2).Value = Me.TextBox3.Value
ws.Cells(rw, 3).Value = Me.TextBox4.Value
ws.Cells(rw, 4).Value = Me.TextBox11.Value
ws.Cells(rw, 5).Value = Me.TextBox12.Value
ws.Cells(rw, 7).Value = Me.TextBox21.Value
ws.Cells(rw, 8).Value = Me.TextBox14.Value
ws.Cells(rw, 9).Value = Me.TextBox17.Value
ws.Cells(rw, 10).Value = Me.TextBox18.Value
ws.Cells(rw, 13).Value = Me.TextBox20.Value
ws.Cells(rw, 19).Value = Me.TextBox30.Value
Me.TextBox1.SetFocus

MsgBox "Your New Client Has Been Added, thank you"

Dim Newfolder As String, path As String
Newfolder = Trim(TextBox4.Text)
path = "C:\Users\Owner\OneDrive\Desktop\Client Records\CSS_Desktop_System\CSS_Admin\Customer_Records\" & Newfolder
 
 With CreateObject("Scripting.FileSystemObject")
 If Not .FolderExists(path) Then .CreateFolder path
 MsgBox "Your New Folder has been Created for you"
End With

TextBox6.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox11.Value = ""
TextBox12.Value = ""
TextBox21.Value = ""
TextBox14.Value = ""
TextBox17.Value = ""
TextBox18.Value = ""
TextBox20.Value = ""
TextBox19.Value = ""

End Sub
Many Many thanks, it works like a dream! So very clever of you.
Regards Advent
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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