Help with VBA for an active X or Forms Control button

[neal1976]

I have a workbook with two Worksheets, let’s call them Sheet1 ad Sheet 2.
On Sheet2 I have a table for which I have created a form with which to add additional rows of data. The table’s cell range is A2:K3
I want to put a either a form control button or an ActiveX button (I don’t understand the difference between the two) on Sheet1 that would do the following upon being clicked:
Open the form on Sheet2 to enter more data rows in table WITHOUT leaving sheet 1
In other words when clicking the button in Sheet1 I would like for the form to open while I still view Sheet1
I already have the button (an ActiveX button) in Sheet1 but I would be happy to change it to a forms button if that would be better. Can you please give me the VBA code to put in the button?

 

[breynolds0431]

Are you trying to show a user form?

 

[neal1976]

yes. but the form is associated with a table in a different page from the page where I want the button to bring the form up

 

[neal1976]

In fact, today i learned how to create a custom for. Perhaps an easier question to ask would be, if I need to create a button (I would need the VBA for this) in Sheet1 that would pull up a form whose date would populate in a table in Sheet2. I want this to be able to happen without ever going into Sheet2. I just want Sheet2 to be where the data would reside.

 

[breynolds0431]

User forms are not necessarily part of a sheet. The form you have sounds like it writes data to a specific sheet, but you can show the form from a button anywhere in your workbook. What you may run into is the sheet switching in the background if you use .Activate or .Select, so turning screen updating off will help avoid that. Your userform would also need to avoid using any ActiveSheet or ActiveCell types of referencing.

NOTE: In the below examples, you will need to rename the ufRowInsert part with the name of your userform.

Here is the code for the activeX button named CommandButton1:

Private Sub CommandButton1_Click()

'set error handling to turn screen updating back on in event of error
On Error GoTo rThings

'turn off screen updating to stop showing other sheet
Application.ScreenUpdating = False

'shows userform in center of screen/monitor
With ufRowInsert '***change the "ufRowInsert" to your userform's button name
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show
End With

'turns screen updating back on
Application.ScreenUpdating = True

Exit Sub

'error handling
rThings:
MsgBox "The below error has occurred: " & vbCrLf & vbCrLf & "Error Number:" & Err.Number & vbCrLf & _
    "Error Description: " & Err.Description
Application.ScreenUpdating = True

End Sub

For a form's button, you can assign it to any macro by right-clicking on it and clicking Assign Macro. For example, in the assign macro dialog, you'd assign to the below sub, ShowForm.

Sub ShowForm()

'set error handling to turn screen updating back on in event of error
On Error GoTo rThings

'turn off screen updating to stop showing other sheet
Application.ScreenUpdating = False

'shows userform in center of screen/monitor
With ufRowInsert
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show
End With

'turns screen updating back on
Application.ScreenUpdating = True

Exit Sub

'error handling
rThings:
MsgBox "The below error has occurred: " & vbCrLf & vbCrLf & "Error Number:" & Err.Number & vbCrLf & _
    "Error Description: " & Err.Description
Application.ScreenUpdating = True

End Sub

 

[neal1976]

thank you. I will try this tomorrow