Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing range name in a macro (2003 SP2)

    Good morning

    In this post, <post#=687402>post 687402</post#> , Steve kindly helped me out with a macro, I am now trying to get this working in a 'live' WB, the range is actually called LHRStaff and not Staff as per the example below, I would have imagined that simply inserting LHR before the name Staff to give me - For Each oCell In ThisWorkbook.Names("LHRStaff").RefersToRange - but it does not, when the user form opens and I click on the Employee Combo nothing shows in the list, any ideas please?

    Private Sub UserForm_Initialize()
    Dim oCell As Range
    CboEmployee.Clear
    For Each oCell In ThisWorkbook.Names("Staff").RefersToRange
    CboEmployee.AddItem CStr(oCell.Value) Next

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing range name in a macro (2003 SP2)

    Since this is a direct follow-up to that post, it would have been better to ask your question in that thread than to start a new thread, forcing Loungers to look at the other thread before knowing what you're talking about.

    Make sure that LHRStaff does refer to the correct range.
    You might also try this:

    For Each oCell In Worksheets("SomeSheet").Range("LHRStaff")

    where SomeSheet is the name of the sheet containing the range LHRStaff.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changing range name in a macro (2003 SP2)

    Did you update the range name in your workbook to be LHRStaff? Choose Insert, Name, Define and ensure you have a range name called LHRStaff refering to the relavant range of names.
    Regards,
    Rudi

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Changing range name in a macro (2003 SP2)

    In addition to the other comments, is the "LHRStaff" range contiguous? If not, as written it will only look through the first contiguous region. You will have to loop through the areas as well if it is non-contiguous.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing range name in a macro (2003 SP2)

    Hi Rudi

    Thanks but the range is valid and a valid name

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing range name in a macro (2003 SP2)

    Hi Steve

    Thanks, yes the range is contiguous &G&7:$G$67

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing range name in a macro (2003 SP2)

    Thanks Hans

    The reason I posted like that I thought if I didn't refer to the older post further questions behind the code may have been asked, and the reason that I thought to post as a seperate issue because it was to do with the range and not the code working (it worked in the example WB in the original post and all I have done is to change the range name to the one in this WB) but point taken for next time.

    That said, changing it as you suggested has still not cured it: For - Each oCell In Worksheets("2008").Range("LHRStaff") - the range does exist and the worksheet 2008 is where I am trying to search so I am a bit flummoxed

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing range name in a macro (2003 SP2)

    You'll have to try to debug the code.
    Click in the line For Each oCell In ...
    Press F9 to set a breakpoint. The line will be highlighted in brown.
    Next time you run the code, execution will pause at the highlighted line, now highlighted in yellow.
    The title bar of the Visual Basic Editor window will show [break] to indicate that execution is pausing.
    You can now single-step through the code: each time you press F8, one instruction will be executed and the yellow highlight will move to the next instruction.
    You can inspect the value of an item (if applicable) by hovering the mouse pointer over the item.
    For instance, if you hold the mouse pointer over oCell, you'll see the value of oCell.
    Hopefully, this will enable you to find out what goes wrong.

    BTW, you can resume code execution (if it is halted) by pressing F5, and you can remove a breakpoint by clicking in the line and pressing F9 again - it is a toggle.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Changing range name in a macro (2003 SP2)

    What do you get in the VBA Immediate window when you type:
    ? Worksheets("2008").Range("LHRStaff").referstorange

    followed by an <enter>?

    Is it the range you expect to be be looking in?

    Steve

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing range name in a macro (2003 SP2)

    Hi Hans

    I am having a bit of trouble with this at the moment, I have not long been in, I think I will try again after the first cold beer has kicked in

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing range name in a macro (2003 SP2)

    Hi Steve

    Excuse my ignorance 'VBA Immediate Window', I am sorry I am not familiar with the term and the MS help does not respond with anything, if I am right in making an assumption that if I am in the code window for the form in question and copy and paste it in there it replaces the ? with print, if I then change the word print to a ? and at the end of the line hit enter the ? again changes to the word print but nothing else

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing range name in a macro (2003 SP2)

    If you're in Excel, press Alt+F11 to activate the Visual Basic Editor.
    In the Visual Basic Editor, press Ctrl+G to activate the Immediate window.
    This is a window where you can execute instructions without having to write a complete macro: type the instruction, then press Enter.

  13. #13
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing range name in a macro (2003 SP2)

    Thanks Hans

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  14. #14
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing range name in a macro (2003 SP2)

    Good morning

    Using the immediate window I get an error as noted below, any further ideas?

    Cheers

    Steve
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changing range name in a macro (2003 SP2)

    Hi Steve,

    Just check for any spelling errors in the two lines below. If the code is debugging at this position then the only thing that can cause this type of error: "Not supporting a property or method" will be found here:

    For Each oCell In ThisWorkbook.Names("Staff").RefersToRange
    CboEmployee.AddItem CStr(oCell.Value)

    Ensure that the bolded value(s) are spelt correctly. Its the only position in your code block where you report the error that can product Err no. 438.
    Regards,
    Rudi

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •