Assign a Userform to a control button on a worksheet

Nitehawkhp

New Member
Joined
Dec 2, 2016
Messages
37
I need help creating a control button on my worksheet named 'Data Validation'. That will open a Userform named 'AdjustResetButtonForm' containing the follow code:


Code:
    TextBox1.Value = Sheets("Data Validation").Range("X3").Value
    TextBox2.Value = Sheets("Data Validation").Range("X4").Value
    TextBox3.Value = Sheets("Data Validation").Range("X5").Value
    TextBox4.Value = Sheets("Data Validation").Range("X6").Value
    TextBox5.Value = Sheets("Data Validation").Range("X7").Value
    TextBox6.Value = Sheets("Data Validation").Range("X8").Value
    TextBox7.Value = Sheets("Data Validation").Range("X9").Value
    TextBox8.Value = Sheets("Data Validation").Range("X10").Value
    TextBox9.Value = Sheets("Data Validation").Range("X11").Value
    TextBox10.Value = Sheets("Data Validation").Range("X12").Value
    TextBox11.Value = Sheets("Data Validation").Range("X13").Value
    TextBox12.Value = Sheets("Data Validation").Range("X14").Value
    AdjustResetButtonForm.Show
Thanks for any help.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi Nitehawkhp,

You just need the following in a standard module to open your form (not at the bottom of the form code you currently have):

Code:
AdjustResetButtonForm.Show

You can also use the following more succinct code to populate the text boxes:

Code:
Option Explicit
Private Sub UserForm_Activate()

    Dim lngCounter As Long
    
    Application.ScreenUpdating = False
    
    For lngCounter = 1 To 12
        Controls("TextBox" & lngCounter).Value = Sheets("Data Validation").Range("X" & lngCounter + 2).Value
    Next lngCounter
    
    Application.ScreenUpdating = True

End Sub

HTH

Robert
 
Upvote 0
Robert,

Thank you. I was able to get the Userform open, however, when I click on the exit button on the Userform, I receive the error message

Run-time '91':
Object variable or With block variable not set

I don't understand what I need to fix this error.

Regards,

Rod
 
Upvote 0
Hi Rod,

I'm not sure as I tested my code and it worked fine :confused:

What code is causing the issue?

Robert
 
Upvote 0
Hi Rod,

I'm not sure as I tested my code and it worked fine :confused:

What code is causing the issue?

Robert

Robert,

I found the issue with the error message.

I don't understand where this code should be placed?


Code:
Option Explicit
Private Sub UserForm_Activate()

    Dim lngCounter As Long
    
    Application.ScreenUpdating = False
    
    For lngCounter = 1 To 12
        Controls("TextBox" & lngCounter).Value = Sheets("Data Validation").Range("X" & lngCounter + 2).Value
    Next lngCounter
    
    Application.ScreenUpdating = True

End Sub

When I copy and paste it into a module window, the "Option Explicit" is not included in the macro and when the macro
executes it gets an error message:

Compile error:

Sub or Function not defined

The word controls is highlighted in the line: Controls("TextBox" & lngCounter).Value = Sheets("Data Validation").Range("X" & lngCounter + 2).Value
Thanks for continuing to help me

Rod
 
Last edited:
Upvote 0
I don't understand where this code should be placed?

This code needs to be part of the form not put a standard module.

Copy the code, right-click on the form in the Visual Basic Editor (VBE) click View Code and paste the code in that module.
 
Upvote 0
It was just me so thank you and you're welcome :)

Something has gone wrong! I can't get the Userform to open.
I have continued to work on the Userform, and now it has stopped working.
I tried using the 'undo' to remove the modifications, but I still receive the
error message:

Run-time error '424':

Object required
When I click the button on the worksheet.
The worksheet button is assigned to the macro
Code:
Sub UserForm_Click()

    AdjustResetButtonForm.Show
   
End Sub
Which is located in a window titiled "Parts Warehouse.xlsm - Module5 (Code), with
two drop down boxes that has (General) in the first box and UserForm_Click in the second.

How do I correct this? I'm so lost and confused right now.:confused:

Rod
 
Upvote 0
Hi Rod,

It's very hard to know what's gone wrong without seeing your workbook but at a guess I'd say the form name has changed from "AdjustResetButtonForm". Double check the form it is spelt correctly and try again. The other possibility is that there is no form on the workbook at all.

HTH

Robert
 
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

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