none
UserForm Initialize - Method 'Range' of object '_Global" failed RRS feed

  • Question

  • I am assigning values to controls on my UserForm as part of the initialize sub routine of the UserForm.

    I have a named range called INP_EV, which refers to a formula I have put together. The formula currently evaluates to 10. I created this named range through the name manager and it has 'Workbook' scope.

    Debugging my 'initialise' sub routine, I receive the Method 'Range' of object '_Global" failed error message at the following line:

    tb_ACQ_EV.value = Range("INP_EV").value

    I am trying to assign the value of the named range INP_EV to a textbox object which is on my UserForm and is named tb_ACQ_EV. It seems as if there's some issue in finding/computing the value of the named range INP_EV.

    I have tried entering the current INP_EV formula into a cell and referring the INP_EV named range to that cell. I do not receive the error message in this case, but I would prefer not to find cells for all of my 'formula'-type names ranges.

    Thanks in advance.

    Wednesday, January 27, 2016 7:20 AM

All replies

  • Option 1: specify the name of the worksheet that contains the cell names INP_EV:

    tb_ACQ_EV.Value = Worksheets("SomeSheet").Range("INP_EV").Value

    Option 2: use the Names collection of the workbook:

    tb_ACQ_EV.Value = ActiveWorkbook.Names("INP_EV").RefersToRange.Value


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by André Santo Wednesday, January 27, 2016 1:02 PM
    Wednesday, January 27, 2016 9:13 AM
  • Thanks André. Much appreciated.

    Option 2 seems what I'm looking for, though I am getting an Application-defined or object-defined error.

    Seems to be unable to compute the formula. However, the below is working.

    tb_ACQ_EV.Value = Evaluate(ActiveWorkbook.Names("INP_EV").Value)

    Is there any issue with this way of doing it? Do you foresee any issues with this approach?

    Thanks again for your help.

    Wednesday, January 27, 2016 11:12 PM
  • That should be fine.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, January 28, 2016 6:03 AM