none
UserForm Set as Object Failure. RRS feed

  • Question

  • I have an Excel 2016 VBA enabled project which has the capability of recalling data into a UserForm to then enable changes by the user, for then replacing existing data in a worksheet.

    The UserForm is re-populated with the recalled data via Design mode.

    The problem occurs when trying to clear the UserForm with the following code.

    Private Sub tgbCancel_Click()
    'Cancel Date Entry Form

    Unload Me
    Call ClearForm1
        
    End Sub

    Sub ClearForm1()

        Dim u           As UserForm
        Dim ctrl        As Variant
        
        Set u = ThisWorkbook.VBProject.VBComponents("frmDataEntry1").Designer
              For Each ctrl In u.Controls
                If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
                    ctrl.Value = ""
                    ctrl.BackColor = &H80000005
                End If
           Next ctrl
           u.Unused.BackColor = &HC0C0C0
           
    End Sub

    The error message states:

    Run-time error'91':

    Object variable or With block variable not set.

    When the ClearForm1 procedure is manually instigated the clearance works perfectly.

    Can anyone explain why calling the ClearForm1 fails when called via VBA, despite Unload Me being used before the call?

    A usable work around would also be much appreciated

    Sunday, September 24, 2017 6:01 AM

Answers

  • You're right...

    But I still think it has to do with the fact, that you Unload the UserForm before calling the macro - once the UserForm is unloaded, the next code line, calling 'ClearForm1' will fail...

    What happens, if you remove 'Unload Me' and in the 'ClearForm1' macro place the Unload instead?

    It should be 'Unload frmDataentry', and perhaps you'll need an 'On Error Resume Next' too. 

    • Marked as answer by John_Howard Tuesday, September 26, 2017 4:23 AM
    • Unmarked as answer by John_Howard Tuesday, September 26, 2017 4:27 AM
    • Marked as answer by John_Howard Tuesday, September 26, 2017 4:32 AM
    Monday, September 25, 2017 6:18 AM
  • Hello,

    Although we use Unload method to unload the form, the next line call clearform1 would still execute like the form is still loaded because it belongs to CommandButton_Click event. The button is still in the memory, so the form is still in memory and would actually unload after the CommandButton_Click event finishes. 

    I think there is no method to work around.

    If you want to make permanent changes to UserForm, please call clearform1 in another form or module.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by John_Howard Tuesday, September 26, 2017 4:07 AM
    Monday, September 25, 2017 9:23 AM
    Moderator

All replies

  • You could load the UserForm (not show it), with something like

    Load frmDataEntry

    ...before clearing the data.

    The UserFrom will be loaded, thus visible by VBA, but invisible to the user, and you can do everything, that you normally can with an UserForm.

    If, you want to show it after clearing you just use frmDataEntry.Show
    If you want to unload it, after clearing, you just use Unload frmDataEntry

    Sunday, September 24, 2017 8:44 AM
  • Wait...

    ...is the [tgbCancel_Click] on the UserForm?!?

    If you Unload the UserForm, you can not perform actions on it!

    Why do you unload the UserForm before calling 'ClearForm1'?!?

    What are you trying to achive?

    if the 'ClearForm1' in a macro in the UserForm object, you don't even have to declare it - you can just use something like:

    For Each CTRL in Me.Controls

    ...and skip the designer all together.

    Sunday, September 24, 2017 8:49 AM
  • Thanks Dan,

    Yes tgbCancel_Click is command button on the UserForm.

    The ClearForm1 is a macro not on the UserForm but separate.

    My understanding is that to  Set the UserForm object in Design mode, the UserForm cannot be in memory.

    Hence the Unload Me in the Cancel routine.

    As the attempt to Set the object is failing, it would seem that Unload Me is not taking the Form out of memory.

    As for skipping Designer, I am further led to believe that designer is required to make permanent changes to UserForm Control values.

    I am wrong in this assumption?

    Monday, September 25, 2017 5:52 AM
  • You're right...

    But I still think it has to do with the fact, that you Unload the UserForm before calling the macro - once the UserForm is unloaded, the next code line, calling 'ClearForm1' will fail...

    What happens, if you remove 'Unload Me' and in the 'ClearForm1' macro place the Unload instead?

    It should be 'Unload frmDataentry', and perhaps you'll need an 'On Error Resume Next' too. 

    • Marked as answer by John_Howard Tuesday, September 26, 2017 4:23 AM
    • Unmarked as answer by John_Howard Tuesday, September 26, 2017 4:27 AM
    • Marked as answer by John_Howard Tuesday, September 26, 2017 4:32 AM
    Monday, September 25, 2017 6:18 AM
  • Hello,

    Although we use Unload method to unload the form, the next line call clearform1 would still execute like the form is still loaded because it belongs to CommandButton_Click event. The button is still in the memory, so the form is still in memory and would actually unload after the CommandButton_Click event finishes. 

    I think there is no method to work around.

    If you want to make permanent changes to UserForm, please call clearform1 in another form or module.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by John_Howard Tuesday, September 26, 2017 4:07 AM
    Monday, September 25, 2017 9:23 AM
    Moderator
  • Dan,

    It looks as though Celeste has the explanation in saying that Calling ClearForm1 from the Form itself, leaves the Form in memory.

    So I have now called ClearForm1 from a separate module and all works well, the only draw back being that I have to leave the Macros unprotected.

    Tuesday, September 26, 2017 4:32 AM
  • Thank Celeste,

    The ClearForm1 is now called at the beginning of the procedure that originally loads the Form.

    This works as intended with the only drawback being the I have to leave the Macros unprotected.

    Tuesday, September 26, 2017 4:34 AM