Assign a Userform to a control button on a worksheet

[Nitehawkhp]

I need help creating a control button on my worksheet named 'Data Validation'. That will open a Userform named 'AdjustResetButtonForm' containing the follow 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.

 

[Trebor76]

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):

AdjustResetButtonForm.Show

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

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

 

[Nitehawkhp]

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

 

[Trebor76]

Hi Rod,

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

What code is causing the issue?

Robert

 

[Nitehawkhp]

Hi Rod,

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

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?

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

 

[Trebor76]

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.

 

[Nitehawkhp]

It works!!!

THANK YOU to all who offered help.

Rod

 

[Trebor76]

It works!!!

THANK YOU to all who offered help.

It was just me so thank you and you're welcome

 

[Nitehawkhp]

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

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.

Rod

 

[Trebor76]

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