Userform prompting message "object variable or with block variable not set"

  • As far as I can tell this userform is doing everything its supposed to do, but when then CommandButton4_Click sub is run I recieve a "Object variable or With block variable not set" message, and for the life of me I can't figure out why.


    This is the code I'm working with.



  • Re: Userform prompting message "object variable or with block variable not set"


    From what I can see of what you are trying to do, this would be more efficient


  • Re: Userform prompting message "object variable or with block variable not set"


    WillR, thanks for the suggestion! You learn something new every day.


    However, I am still recieving the Object variable/With block prompt; it VB doesn't jump to any of my code (like it usually does when there is a problem) but instead goes to the actual userform; is this because there is a problem with the actual form or just because all the subs causing the problem part of the userform?

  • Re: Userform prompting message "object variable or with block variable not set"


    try this amend



    Silly question, but considering the code...Do you have 3 sheets in your workbook ?

  • Re: Userform prompting message "object variable or with block variable not set"


    The problem is caused by the object Userform1 being destroyed before you have finished referencing it.


    Normally your code would go,
    Standard code module

    Code
    1. Sub X()
    2. Load UserForm1
    3. Unload UserForm1
    4. End Sub


    Userform code module

  • Re: Userform prompting message "object variable or with block variable not set"


    WillR, no luck with that change...


    Yes, the workbook has 3 sheets. The data entered into the textboxes and comboboxes has always been correctly added to sheet3, and the userform successfully unloads (the userform displays the value of "x" and it updates correctly when new lines are added to sheet3, as opposed to when the last line of code for Private Sub CommandButton4_Click() is changed to userform1.hide; which only records the value of x when the userform is first initialized).


    Would it help if I uploaded a copy of the workbook I'm testing this in?

  • Re: Userform prompting message "object variable or with block variable not set"


    Oddly enough, creating a new userform and copying my subs into it solved the problem... Its at times like these that I just scratch my head and go drink some coffee.


    Again, thanks for all the help.


    Edit:
    Andy; I didn't see your post until after I created this one, but what you proposed get me thinking, so I went back and checked for macros not in Module1 or Userform2 and found what was causing the problem! I had a button on sheet1 that ran a macro I had made last friday that only did "userform1.show"; I had changed the macro associated with the button to a different sub but must have lost the change when reverting back to a previous version of the workbook...


    userform1.show was being fired once when the button was pressed, and a second time in the UserForm_Initialize() sub. Atleast now I know what was happening. Thanks.


    Also: WillR, thanks again for the x = .[A65536].End(xlUp).Row + 1 bit; VERY useful and probably something I should have learned long ago.


    You guys rock.