1. 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
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

2. 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. 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

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

4. Re: Range Problem (2003 SP3)

Try this:

...
For Each oCell In ThisWorkbook.Names("LeaveTypes").RefersToRange
Next oCell
...

5. 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

Posting Permissions

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