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

    Range Problem (2003 SP3)

    Good afternoon

    I have a user form on a holiday calendar upon which there is a combo for the type of leave days

    Dim ICell As Range
    cboLeaveType.Clear
    For Each oCell In ThisWorkbook.Names("LeaveTypes").RefersToRange
    cboLeaveType.AddItem CStr(oCell.Value)
    Next
    Me.Calendar1 = Date

    The range to which it refers is a horizontal range that heads a number of columns and is tied into lots of other calculations (my initial thought to answer my question which follows was to move the range but that then causes other problems and the WB is very difficult to trim down and post)

    What I would like to do is to have the combo show H,h,S,s,M,m etc. so that I can then use sumproduct formula that Rory showed me in a previous post to do the additions.

    Can anybody see a way that I can achieve this?

    Cheers

    Steve
    Attached Images Attached Images
    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: Range Problem (2003 SP3)

    Sorry, I don't understand your question. What exactly do you mean by "H,h,S,s,M,m etc." and what exactly do you want to use it for?

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

    Re: Range Problem (2003 SP3)

    Hi Hans

    This part of the code

    Dim ICell As Range
    cboLeaveType.Clear
    For Each oCell In ThisWorkbook.Names("LeaveTypes").RefersToRange
    cboLeaveType.AddItem CStr(oCell.Value)

    Allows the Combo to look at the range 'LeaveTypes' which is the range I have shown in the screenshot (H,S,M,P,T,A,U,, I cannot extend the range which would make things easier so that the user could select a Capital letter to represent a whole days leave and a lower case letter to represent a half day leave (H = Holiday = 1 full day, h = holiday = 1/2 day) so the point of the question was is it possible to change the code in the Combo box (or change the Combo to a text box) to accomodate upper and lower carriage entries without extending the range.

    I hope that makes a little for sense.

    Cheers

    Steve
    Cheers

    Steve

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

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

    Re: Range Problem (2003 SP3)

    Try this:

    ...
    For Each oCell In ThisWorkbook.Names("LeaveTypes").RefersToRange
    cboLeaveType.AddItem UCase(oCell)
    cboLeaveType.AddItem LCase(oCell)
    Next oCell
    ...

  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: Range Problem (2003 SP3)

    Thanks Hans

    That was great, the Combo on the form now shows both the Capital and Lower case initials, I can't get what i wanted to work properly but that is probably down to a bad design from a novice but I will plod on

    Thanks again

    Cheers

    Steve
    Cheers

    Steve

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

Posting Permissions

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