cell cahnges based on a value in one cell

[scouse13]

Hi all
on a sheet cell b2 is for data entry, users will type in a number 1 to 5, if 1 is entered, cell d2 will show grade 1, if a 2 is entered in b2,d2 will show grade 1 and cell d3 will show grade 2 continuing down to cells d6 / grade 5
what's the easiest way to do this using vba.

also can a command button caption name be changed to what is showing in a cell ie, if d2 shows grade 1, the command button caption will be grade 1 (the command button will be on a different sheet).

many thanks
scouse13

[davesexcel]

Practice with this one, The code belongs in the worksheet module of sheet1

RightClick.jpg

Please Login or Register  to view this content.

place a commandbutton on sheet2.

In sheet1 change enter one of the numbers in B2 to activate the code.

It might be best to use data validation dropdown in B2 so there is nothing entered incorrectly

[AlphaFrog]

Same as Dave's.
Uses a Form-type command button on Sheet2

Please Login or Register  to view this content.