1

I know very little about VBA, but I'm trying to design a userform for an excel workbook. The idea is click the button, bring up the userform, enter info, hit OK, and your info is formatted correctly and inserted into the worksheet.

I have 3 userforms that all work fine, but any macro I create that references one of them just does not recognize that that particular userform exists.

The code I'm having a problem with is pretty straightforward:

Private Sub LiquidFormButton_Click()

LiquidEntryUserform.Show

End Sub

Edit (Update): So I tried making a new userform, with a different name. I copied and pasted all of the controls from the object over to the new userform, changed the name of the macro to bring up the userform, and voila, it works. However, now the userform itself doesn't do anything because none of the controls actually have any codes behind them telling them what to do. That's fine, I'll just copy over the codes from the broken form and BOOM now it doesn't work. Soooo something in the very very simple coding within the userform itself is preventing it from being shown, even though the new userform AND the broken one both, in fact, do everything else they need to do besides show up. I'll post the full userform code up later on after some dabbling. Thank you!

Tyler Medina
  • 23
  • 1
  • 1
  • 6
  • well that's definitely strange. If the userform is named `LiquidEntryUserform` then the above code should work just fine. Can you make sure the name matches? Can you also stick something like `MsgBox "LiquidFormButton executing"` right before you are trying to load the form to confirm that the `LiquidFormButton_Click` event executes at all? –  Dec 15 '14 at 22:27
  • Yep, I added the message box, and it executed fine... As best as I can tell, the names match up. I tried renaming both to "damnit" as well, but that didn't work either. :/ I guess it's just some kind of corruption or glitch or something. No idea! – Tyler Medina Dec 15 '14 at 23:26

2 Answers2

1

You should 'instantiate' the form like so

Private Sub LiquidFormButton_Click()

    Dim liquid as LiquidEntryUserform ' define a liquid var of the correct type
    Set liquid = new LiquidEntryUserform ' create the Form
    liquid.Show  'show it

    ' here you can still access variables 
    ' on the form
    If liquid.TextBox1.Text = "700" Then 
       'do things
    End if

End Sub

My project looks like this:

enter image description here

You can use the Object Browser (View|Object Browser or hit F2) to find the Forms and Classes you have in your project:

enter image description here

rene
  • 41,474
  • 78
  • 114
  • 152
  • That doesn't seem to have fixed the problem. Before entering your code, I'm also getting an "object variable not set" message. After entering your fix, it comes up with a few other errors, buuuuut that's probably because I don't really know what I'm doing with your code. – Tyler Medina Dec 15 '14 at 18:59
  • Hmm, I've updated my answer slightly so you can see where you should add my code exactly. Is the macro in a different file maybe? Can you add a screenshot of the macro editor? – rene Dec 15 '14 at 19:12
  • Well I tried to upload a picture, but I don't have enough reputation on the site yet. :P Okay so, I did in fact stick your code in the right place. The result was that the debug highlighted "liquid As LiquidEntryUserform" and gave me a compile error: "User-defined type not defined." Is this because the editor still isn't recognizing that the userform exists? Also, the macro is definitely contained within the correct workbook, and appears to be identical in structure to the other two userforms I currently have up and running on the same project. – Tyler Medina Dec 15 '14 at 21:04
  • Yep, mine looks pretty much identical. If I use your code, I still get the "user-defined type not defined" error. However, if I swap out "LiquidEntryUserform" for any other userform, it works just fine. – Tyler Medina Dec 15 '14 at 21:17
  • 1
    Looks like you think you have a LiquidEntryuserform but it is named differently... strange... added a picture of the object browser. My last resort. – rene Dec 15 '14 at 21:32
  • @TylerMedina yeah definitely check the spelling. –  Dec 15 '14 at 22:29
  • I went through the object browser, found "LiquidEntryUserform", copied and pasted the name, but no dice. I think VBA just hates me. Oh well! I'll just try to recreate the userform from scratch. Thanks for the help, everyone! – Tyler Medina Dec 15 '14 at 23:06
0

I had a similar experience after making some changes to a UserForm. I noticed something was actually working immediately prior to the 424 error occurring. Using F8 to step thru my code, it turned out I was asking a control to be configured - that I had deleted!!
I was using this code in the MS XL Objects worksheet (the button is on the associated worksheet)... 

Private Sub cmdTransactions_Click()
 frmTransactions.Show
End Sub

to bring up the UserForm and this code in the Forms module... 

Private Sub UserForm_Initialize()
: : :
Row_Number = [mostrecent].Value
Cells(Row_Number + 1, 2).Select   <<< this bit was happening (.Select works for me!!)
: : :
cmdReset.Enabled = False   <<< a control in the UserForm
chkDeposit.Enabled = False  <<< this control had been deleted!! Remarked out but un-Remarked for clarity 
: : : 
End Sub

Hope that might help someone.

Steve
  • 1