Combobox on a worksheet

[Terry]

Here's what I want to do:
Offer the user the ability to quickly select a print area and print it.

Here's what I've done so far:
Defined [named] a number of ranges, each being a potential print area.
Ranges are called Week_1, Week_2, Week_3 etc
Added an unbound combobox with an input range that draws the named ranges
from a separate worksheet.
Added a command button to print the current print area.

Here's where I'm stuck:
I think I can get to grips with setting the print area in code if i could
just extract the value in the combobox as a variable. I tried adding a cell
link to the combobox in the hope of extracting the value from a cell rather
than the combo. However, the cell it is bound to contains just a [row]
number rather than the value that appears in the combo.

Where am i going wrong?

TIA for any pointers. Access/VBA, I'm used to!
WinXP;O2K

 

[Anne Troy]

Do you need to extract the value as a variable? Why not just name the ranges
the same as the "print areas" you're providing? Then, you can just print the
named range...
<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->
Hope this helps!
Anne Troy (better known as Dreamboat)
Author: Dreamboat on Word
Email: Dreamboat*at*Piersontech.com
Web: www.TheOfficeExperts.com
<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->

 

[Anders S]

Hi Terry,

With the combobox from the Forms Toolbar you get the row number in the Cell Link cell.
Assuming the Cell link is in B1 on the active sheet and the Input Range is in Sheet2!A1:A10, you can try

=INDEX(Sheet2!A1:A10,B1,1)

Another way is to use a combobox from the Control Toolbar and in Design Mode right-click the combobox and define the ListFillRange and LinkedCell properties. The LinkedCell will display the row text of the combobox, not the row number.

HTH
Anders Silven

 

[Terry]

The Ranges *are* the same as the print areas, I want the code to extract the
users choice, such as week 1, week 2, May04, June04 etc. and print it. I
don't want to write an If statement and a line of code for each option. Or
did I miss your point? Sorry if I did

Terry

Do you need to extract the value as a variable? Why not just name the ranges
the same as the "print areas" you're providing? Then, you can just print the
named range...
<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->
Hope this helps!
Anne Troy (better known as Dreamboat)
Author: Dreamboat on Word
Email: Dreamboat*at*Piersontech.com
Web: www.TheOfficeExperts.com
<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->

Here's what I want to do:
Offer the user the ability to quickly select a print area and print it.

Here's what I've done so far:
Defined [named] a number of ranges, each being a potential print area.
Ranges are called Week_1, Week_2, Week_3 etc
Added an unbound combobox with an input range that draws the named ranges
from a separate worksheet.
Added a command button to print the current print area.

Here's where I'm stuck:
I think I can get to grips with setting the print area in code if i could
just extract the value in the combobox as a variable. I tried adding a cell
link to the combobox in the hope of extracting the value from a cell rather
than the combo. However, the cell it is bound to contains just a [row]
number rather than the value that appears in the combo.

Where am i going wrong?

TIA for any pointers. Access/VBA, I'm used to!
WinXP;O2K

 

[Terry]

I'll take a look at Help for INDEX, thanks.

Meanwhile, the Control toolbar is just what I wanted. I have been using the
Forms toolbar up to now, I must have thought something along the lines of 'I
use Access Forms... what would be the Excel equivalent? ... Ah, a Forms
toolbar... that must be it'. It does look near identical tho, to be fair to
me!.

Thanks a lot for that, that's terrific. I now have my combo and command
button working as expected.

Terry

Hi Terry,

With the combobox from the Forms Toolbar you get the row number in the Cell
Link cell.
Assuming the Cell link is in B1 on the active sheet and the Input Range is
in Sheet2!A1:A10, you can try

=INDEX(Sheet2!A1:A10,B1,1)

Another way is to use a combobox from the Control Toolbar and in Design Mode
right-click the combobox and define the ListFillRange and LinkedCell
properties. The LinkedCell will display the row text of the combobox, not
the row number.

HTH
Anders Silven

 

[Anders S]

Hi Terry,

Thank you for the feedback.

Never mind about the INDEX() part if you're OK with the Control Toolbar combobox.

Here's a link that clears some of the fog: http://j-walk.com/ss/excel/tips/tip84.htm

I believe the Forms controls are for backward compatibility. Also Forms controls work cross platform Macintosh/Windows.

Best regards
Anders Silven

I'll take a look at Help for INDEX, thanks.

Meanwhile, the Control toolbar is just what I wanted. I have been using the
Forms toolbar up to now, I must have thought something along the lines of 'I
use Access Forms... what would be the Excel equivalent? ... Ah, a Forms
toolbar... that must be it'. It does look near identical tho, to be fair to
me!.

Thanks a lot for that, that's terrific. I now have my combo and command
button working as expected.

Terry

Hi Terry,

With the combobox from the Forms Toolbar you get the row number in the Cell
Link cell.
Assuming the Cell link is in B1 on the active sheet and the Input Range is
in Sheet2!A1:A10, you can try

=INDEX(Sheet2!A1:A10,B1,1)

Another way is to use a combobox from the Control Toolbar and in Design Mode
right-click the combobox and define the ListFillRange and LinkedCell
properties. The LinkedCell will display the row text of the combobox, not
the row number.

HTH
Anders Silven

Here's what I want to do:
Offer the user the ability to quickly select a print area and print it.

Here's what I've done so far:
Defined [named] a number of ranges, each being a potential print area.
Ranges are called Week_1, Week_2, Week_3 etc
Added an unbound combobox with an input range that draws the named ranges
from a separate worksheet.
Added a command button to print the current print area.

Here's where I'm stuck:
I think I can get to grips with setting the print area in code if i could
just extract the value in the combobox as a variable. I tried adding a cell
link to the combobox in the hope of extracting the value from a cell rather
than the combo. However, the cell it is bound to contains just a [row]
number rather than the value that appears in the combo.

Where am i going wrong?

TIA for any pointers. Access/VBA, I'm used to!
WinXP;O2K

 

[Terry]

Thanks for the j-walk link, Anders. There's some good stuff there.

Hi Terry,

Thank you for the feedback.

Never mind about the INDEX() part if you're OK with the Control Toolbar
combobox.

Here's a link that clears some of the fog:
http://j-walk.com/ss/excel/tips/tip84.htm

I believe the Forms controls are for backward compatibility. Also Forms
controls work cross platform Macintosh/Windows.

Best regards
Anders Silven

I'll take a look at Help for INDEX, thanks.

Meanwhile, the Control toolbar is just what I wanted. I have been using the
Forms toolbar up to now, I must have thought something along the lines of 'I
use Access Forms... what would be the Excel equivalent? ... Ah, a Forms
toolbar... that must be it'. It does look near identical tho, to be fair to
me!.

Thanks a lot for that, that's terrific. I now have my combo and command
button working as expected.

Terry

Hi Terry,

With the combobox from the Forms Toolbar you get the row number in the Cell
Link cell.
Assuming the Cell link is in B1 on the active sheet and the Input Range is
in Sheet2!A1:A10, you can try

=INDEX(Sheet2!A1:A10,B1,1)

Another way is to use a combobox from the Control Toolbar and in Design Mode
right-click the combobox and define the ListFillRange and LinkedCell
properties. The LinkedCell will display the row text of the combobox, not
the row number.

HTH
Anders Silven

Here's what I want to do:
Offer the user the ability to quickly select a print area and print it.

Here's what I've done so far:
Defined [named] a number of ranges, each being a potential print area.
Ranges are called Week_1, Week_2, Week_3 etc
Added an unbound combobox with an input range that draws the named ranges
from a separate worksheet.
Added a command button to print the current print area.

Here's where I'm stuck:
I think I can get to grips with setting the print area in code if i could
just extract the value in the combobox as a variable. I tried adding a cell
link to the combobox in the hope of extracting the value from a cell rather
than the combo. However, the cell it is bound to contains just a [row]
number rather than the value that appears in the combo.

Where am i going wrong?

TIA for any pointers. Access/VBA, I'm used to!
WinXP;O2K