Userform command button help

Elliottj2121

Board Regular
Joined
Apr 15, 2021
Messages
50
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello

Any help would be greatly appreciated. I am new to VBA code and I am looking for some help. I have created a workbook template that users will use to enter in trade reference data. In the workbook, I have a userform, frmapplicantname, that starts when the workbook is open prompting the user to enter in an applicant name . When the user enters the name and clicks submit I am trying to achieve these automated steps:

1. The applicant name is copied into sheet 1 cell B2.
2. A copy of the workbook is made with the data in Sheet1 Cell B2 and is named what ever is in Sheet1 Cell B2.
3. The newly named copy is then saved to a specific folder on the user's desktop.
4. When the user opens the newly named workbook the userform frmapplicantname doesn't run becaue there is data in Sheet1 Cell B2.

This is the code I have written for the command button for userform frmapplicantname, but I keep getting a error in the sub SavePath(), Line "thisworkbook.SaveCopyAs ([myfoldername2....


Private Sub cmdsubmit_Click()

FillText
SavePath
Unload Me
ActiveWorkbook.Close savechanges:=False

End Sub
Sub FillText()

Dim sh1 As Worksheet
Set sh1 = ThisWorkbook.Sheets(1)


With sh1
.Cells(1, 2) = frmapplicantname.txtapplicantname

End With

End Sub


Sub SavePath()

Dim myFolderName2 As String
Dim myFileName2 As String

myFolderName2 = Environ("userprofile") & "\Desktop\Steel_Group_References\"

If (Dir(myFolderName2, vbDirectory)) = "" Then MkDir myFolderName2

myFileName2 = txtapplicantname.Value & "" & "Steel Group References"

ThisWorkbook.SaveCopyAs ([myFolderName2 & "\" & myFileName2])

End Sub


Thank you!!!!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Am looking into it. Will post something in a little while.
 
Upvote 0
Here is some code to help you on your way:

In the Workbook_Open event routine put:
VBA Code:
Option Explicit

Private Sub Workbook_Open()
  Dim myFolderName2 As String, myFileName2 As String
  If Me.Sheets(1).Cells(1, 2) = "" Then
    frmApplicantName.Show
    If Me.Sheets(1).Cells(1, 2) <> "" Then
      myFolderName2 = Environ("userprofile") & "\Desktop\Steel_Group_References\"
      If (Dir(myFolderName2, vbDirectory)) = "" Then MkDir myFolderName2
      myFileName2 = Me.Sheets(1).Cells(1, 2).Value & " " & "Steel Group References.xlsm"
      Me.SaveCopyAs (myFolderName2 & myFileName2)
    End If
    Me.Close savechanges:=False
  End If
End Sub

in the frmApplicantName form code put:

VBA Code:
Option Explicit

Private Sub cmdSubmit_Click()
  ThisWorkbook.Sheets(1).Cells(1, 2) = frmApplicantName.txtapplicantname
  Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = VbQueryClose.vbFormControlMenu Then
    ThisWorkbook.Sheets(1).Cells(1, 2) = ""
    Unload Me
  End If
End Sub

Note most of the code you had I reworked and put into the Workbook_Open event. I added code to check if Cells(1,2) is blank; if so then display form and process; if is not blank then exit. Added the UserForm_QueryClose event sub to catch click of close X on the user form and close safely. Hope this woks for you. Let me know if your need more help.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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