none
Assign existing range name to a new range object RRS feed

  • Question

  • Hi!

    I have a small application with 12 ranges (1 for each month). I need to show the  content of only one range in a simple Userform, which I do now, using a complex code :

    Dim MyRange as Range

    Case cbox1.ListIndex

    Case 1

     MyRange = "Month1"

    Case 2

    MyRange = "Month2"

    etc .......

    end Select

    I wanted to simplify my code , replacing the Select part with:

    Dim rngName as String

    rngName = "Month" & Str(cbox1.ListIndex)

    MyRange = rngName

    And it does not work!

    I tried to use "Set ...

    MyRange.Name =

    MyRange.Name.Name =

    Alas!

    I get a message:

    "Run-time error 91: Object variable or With block variable not set

    can you help me with this?


    Plamen

    Wednesday, January 7, 2015 9:37 PM

Answers

  • Thank you Hans!

    Actually it did not work as you proposed, the code compiled well but the error appeared when in the next statement:

     Range(MyRange).CopyPicture Appearance:=xlScreen, Format:=xlPicture
    .......

    I just used in my further code the phrase:

    Range("Month" & Me.cbox1.ListIndex), instead of Range(MyRange) .... and it worked!


    Plamen

    • Marked as answer by PlamenGo Wednesday, January 7, 2015 10:58 PM
    Wednesday, January 7, 2015 10:58 PM

All replies

  • Try

    Set MyRange = Range("Month" & Me.cbox1.ListIndex)


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

    Wednesday, January 7, 2015 9:55 PM
  • Thank you Hans!

    Actually it did not work as you proposed, the code compiled well but the error appeared when in the next statement:

     Range(MyRange).CopyPicture Appearance:=xlScreen, Format:=xlPicture
    .......

    I just used in my further code the phrase:

    Range("Month" & Me.cbox1.ListIndex), instead of Range(MyRange) .... and it worked!


    Plamen

    • Marked as answer by PlamenGo Wednesday, January 7, 2015 10:58 PM
    Wednesday, January 7, 2015 10:58 PM
  • Since you declare MyRange as a Range, not as a String, you could have used

    Set MyRange = Range("Month" & Me.cbox1.ListIndex)
    MyRange.CopyPicture Appearance:=xlScreen, Format:=xlPicture


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

    Thursday, January 8, 2015 5:41 AM