Hello All,

I am creating a form templet and would like to have options for which price
sheet my form references. There are 6 price sheets (List, 5%, 10%, 15%, 20%,
Broker) They are all formatted the same, so the row and columns are the
same, just different values. My form is currently linked to 'Current Price
Sheet' (I thought at the time we would just drop the correct price sheet into
the folder and name it 'current price sheet' no matter which. This answer is
un-neat and un-fun.)

Is there a way to include some sort of choice box or option buttons on the
form that will switch price sheet references depending on the form-user's
choice?

All help is appreciated as, (I'm sure you already know), I do not know what
I am doing:)
Sincerely, Miss S.

Hi Miss S.

Within VBA you can reference a worksheet in a number of ways. You can use
the number of the sheet within the workbook, ex. 'Sheets(1)....', or you can
use the name of the worksheet on it's tab, ex. 'Sheets("Sheet 1")...', or you
can use the name of the worksheet as it appears in the Project Explorer in
the VBA Editor, ex. 'Sheet1....'

So, you could put a list box on your form and then use their selection to
reference the appropriate sheet. You could either use either the list index
with method one above, or the selected value (assuming it matches the
worksheet tab exactly) with method two above. Alternatively, you could also
use radio buttons and a select case statement with method three above.
Personally, I think it would be best to use one of the first two methods.

If you'd like to discuss this further, please feel free to e-mail me,
HTH,
tdw

Sorry, my signature wasn't appended to my previous post ...


Timothy White
Contract Programmer
Ontario, Canada

<my initialshite<atsympatico<dot<countryCode

Hi tdw, Thank you for your kindly attempt to help me:)

I was expecting an answer with something referring to from 'edit' menu
'links...' change link source. Everytime I did that, my plain cells with
formulas referring to the price sheet showed the new link; but the combo
boxes no longer worked as a combo box... they became objects that brought up
the picture toolbar. Thanks to your response, I realize now I should change
them somehow in the dreaded vba. (I don't know how to use it) My combo boxes
are linked by naming link in properties menu. I see the workbook with the 6
pricesheets in the vba, but I still don't understand how to make a list box
selection automatically change all these combo boxes (85) AND change the
plain cells that also use the pricesheets. AAAAHHHGGG! I have looked and
poked at this all day. Please, what am I missing?

Thanks again, Miss. S.


"tdw" wrote:

Hi Miss S.

Within VBA you can reference a worksheet in a number of ways. You can use
the number of the sheet within the workbook, ex. 'Sheets(1)....', or you can
use the name of the worksheet on it's tab, ex. 'Sheets("Sheet 1")...', or you
can use the name of the worksheet as it appears in the Project Explorer in
the VBA Editor, ex. 'Sheet1....'

So, you could put a list box on your form and then use their selection to
reference the appropriate sheet. You could either use either the list index
with method one above, or the selected value (assuming it matches the
worksheet tab exactly) with method two above. Alternatively, you could also
use radio buttons and a select case statement with method three above.
Personally, I think it would be best to use one of the first two methods.

If you'd like to discuss this further, please feel free to e-mail me,
HTH,
tdw