Userform/Command Button to Appear in New Worksheet

[Mr_Buttons]

Hi everyone!

I made a userform that collects and transfers data to a certain worksheet. I assigned a button to open the worksheet in that specific page.

How can I get that userform or command button to appear in a newly created worksheet? This is so that I don't have to create a command button whenever I create a new worksheet. My code is as per below:

Sub Button4_Click()
    'button to open worksheet
    qualform.Show
End Sub

Option Explicit
Private Sub UserForm_Initialize()
With Me
 .StartUpPosition = 1
 .Width = Application.Width * 0.3
 .Height = Application.Height * 0.5
 .Left = Application.Left + (Application.Width * 0.5) \ 1
 .Top = Application.Top + (Application.Height * 0.5) \ 1
 End With
'fill date drop down box - 1 to 31
With cmbdate
      .AddItem "1"
      .AddItem "2"
      .AddItem "3"
      .AddItem "4"
      .AddItem "5"
      .AddItem "6"
      .AddItem "7"
      .AddItem "8"
      .AddItem "9"
      .AddItem "10"
      .AddItem "11"
      .AddItem "12"
      .AddItem "13"
      .AddItem "14"
      .AddItem "15"
      .AddItem "16"
      .AddItem "17"
      .AddItem "18"
      .AddItem "19"
      .AddItem "20"
      .AddItem "21"
      .AddItem "22"
      .AddItem "23"
      .AddItem "24"
      .AddItem "25"
      .AddItem "26"
      .AddItem "27"
      .AddItem "28"
      .AddItem "29"
      .AddItem "30"
      .AddItem "31"
    End With
'Fill Month Drop Down box - Takes Jan to Dec
   With cmbmonth
      .AddItem "JAN"
      .AddItem "FEB"
      .AddItem "MAR"
      .AddItem "APR"
      .AddItem "MAY"
      .AddItem "JUN"
      .AddItem "JUL"
      .AddItem "AUG"
      .AddItem "SEP"
      .AddItem "OCT"
      .AddItem "NOV"
      .AddItem "DEC"
    End With
  'Fill Year Drop Down box - Takes 1980 to 2014
   With cmbyear
      .AddItem "2020"
      .AddItem "2021"
      .AddItem "2022"
      .AddItem "2023"
      .AddItem "2024"
      .AddItem "2025"
      .AddItem "2026"
      .AddItem "2027"
      .AddItem "2028"
      .AddItem "2029"
      .AddItem "2030"
      .AddItem "2031"
      .AddItem "2032"
      .AddItem "2033"
      .AddItem "2034"
      .AddItem "2035"
      .AddItem "2036"
      .AddItem "2037"
      .AddItem "2038"
      .AddItem "2039"
      .AddItem "2040"
    End With
    
'Fill chart with a list
 With cmbchart
 .AddItem "Location Chart"
 .AddItem "Spread Chart"
 .AddItem "Others"
    End With
    
'Fill type of trigger
With cmbtrigger
 .AddItem "Fail Alert Limit UCL"
 .AddItem "Fail Control Limit UCL"
 .AddItem "Fail Spec Limit"
  End With
  
With txtdateac
    txtdateac.Text = Format(Now(), "DD/MM/YYYY")
    End With
End Sub
Private Sub btncancel_Click()
   Unload Me
End Sub

Private Sub btnsubmit_Click()
'Copy input values to sheet.
Dim irow As Long

'Determine empty row
' emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
With Sheet1
irow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(irow, 1).Value = Me.cmbdate.Value & "/" & Me.cmbmonth.Value & "/" & Me.cmbyear.Value
        .Cells(irow, 2).Value = Me.txtbatch.Value
        .Cells(irow, 3).Value = Me.cmbchart.Value
        .Cells(irow, 4).Value = Me.cmbtrigger.Value
        .Cells(irow, 5).Value = Me.txtfail.Value
        .Cells(irow, 6).Value = Me.txtdisreview.Value
        .Cells(irow, 7).Value = Me.txtdateac.Value
        .Cells(irow, 8).Value = Me.txtempid.Value
End With
'Clear input controls.
        Me.cmbdate.Value = ""
        Me.cmbmonth.Value = ""
        Me.cmbyear.Value = ""
        Me.txtbatch.Value = ""
        Me.cmbchart.Value = ""
        Me.cmbtrigger.Value = ""
        Me.txtfail.Value = ""
        Me.txtdisreview.Value = ""
        Me.txtdateac.Value = ""
        Me.txtempid.Value = ""
End Sub

Appreciate the suggestions and your help. Thanks yall'!

[#2 · Accepted solution]

@Mr_Buttons

Hi,

Instead of the embedded button on the worksheet, you can create a macro in the Personal Macro Workbook to open the UserForm and add this macro to the Quick Access Toolbar or in a new custom ribbon tab by following the steps in the link below:

https://support.office.com/en-us/article/assign-a-macro-to-a-button-728c83ec-61d0-40bd-b6ba-927f84eb...

Also, you need to modify the VBA code to make it applied to all worksheets, by replacing:

Sheet1

with:

ActiveSheet

Hope that helps

[#3]

@Haytham Amairah 

Hi there,

Thank you so much! It worked perfectly!