assigning a value to a variable

[Phillip2]

I have a worksheet with 100 buttons. Each button represents a Customer’s record number. I’m working on a macro to assign to each button that when clicked would do the following:

• Open the userform CCX_Form
• Assign the customer’s record number to the variable TBrecord
• Run the subroutine Private Sub Search_Click()

My form opens, but I’m getting an error 424 (Object Required) when using the following code. What am I missing?

Sub Rectangle1_Click()
CCX_Form.Show
TBrecord.Value = "001"
Call Sub Search
End Sub

Thank you in advance for your time and help.

 

[Fluff]

You need to specify that the textbox is on the userform

Sub Rectangle1_Click()
With CCX_Form
   .Show
   .TBrecord.Value = "001"
End With
Call Sub Search
End Sub

 

[Snakehips]

@Phillip2 You are calling Search but your Sub is named Search_Click ?

 

[Phillip2]

Thank you so much. I'll try that.

 

[Phillip2]

I tried the code and I’m getting errors on the Call Sub Search line. The first time I got a Syntax error then it began giving me a Compile error: Expected: identifier.

I took that line out and the routine ran without error, but my Record Number textbox (TBrecord) was blank. So, I reversed the order having it set the variable first then showing the form and it worked great.

So I down to the Call Sub Line.

I think my problem with the Search routine is like the one before. The Search routine is inside the form. (The buttons are on a worksheet and their code is written in a module.) I tried moving the Call Sub into the With Statement but that didn’t work.

Any ideas?

 

[Fluff]

Did you try what Snakehips said in post#3?

 

[Phillip2]

I did and I got the same error. By the Snakehips I failed to thank you for you input. Things got a little crazy around here.

 

[Fluff]

Why not remove that from the sub & just put Search_Click in the userform_Initalize code

 

[Phillip2]

I tried putting Search_Click in the Initialize Code, and it didn't work either. I went back to the original plan and here is a picture if what I'm seeing.

 

[Fluff]

How did you put it in the initialize event?