Hide or Unhide certain columns based on a cell value

Hello All

I have a worksheet which calculates payback percentages for Slot
machines, based on how many coins a person plays. Since machines vary
in the amount of coins they are allowed to play, I have the sheet set
up from one coin up to five coins. On the sheet, if they enter the
amount of coins, it should hide the columns that are not needed... eg:
If it is 3 coin machine, it will hide the calculation columns for the
fourth and fifth coin. If, after that, they change the number to 2
coins, it should hide the third coin calculation column, and so on. I
tried achieving this with a Case... Select approach, but I have having
no luck at all.
If anyone has some sort of basic code that hides a column in the same
sheet, based on a numeric input in a cell on that same sheet, please
help. Thank you for your time.

With a Worksheet_Change event procedure:

'-----------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lColumn As Long
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
For lColumn = 2 To 5
Columns(lColumn).Hidden = lColumn > Range("A2").Value
Next lColumn
End Sub
'-------------------------------------------------------------------

See example: http://cjoint.com/?hkljrxXed6

HTH
--
AP

<[email protected]> a écrit dans le message de news:
[email protected]...
> Hello All
>
> I have a worksheet which calculates payback percentages for Slot
> machines, based on how many coins a person plays. Since machines vary
> in the amount of coins they are allowed to play, I have the sheet set
> up from one coin up to five coins. On the sheet, if they enter the
> amount of coins, it should hide the columns that are not needed... eg:
> If it is 3 coin machine, it will hide the calculation columns for the
> fourth and fifth coin. If, after that, they change the number to 2
> coins, it should hide the third coin calculation column, and so on. I
> tried achieving this with a Case... Select approach, but I have having
> no luck at all.
> If anyone has some sort of basic code that hides a column in the same
> sheet, based on a numeric input in a cell on that same sheet, please
> help. Thank you for your time.
>

Thanx for the quick response, but the procedure does not do exactly
what I intended.

OK, my target cell is J7... this is where I input either the number 2,
3 or 4 , and depending on what I input, the macro should hide certain
columns.
If J7 is 2, then I want to hide columns M to Z.
If J7 is 3, then I want to hide columns R to Z
If J7 is 4, then I want ot hide columns W to Z

Also, If I enter the number 2 in J7, and columns M to Z are hidden, at
a later stage if I enter 3, it should unhide M:P, and so on.

Sorry if I was unclear during my first email.

Sachin

Ardus Petus wrote:
> With a Worksheet_Change event procedure:
>
> '-----------------------------------------------------------
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim lColumn As Long
> If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
> For lColumn =3D 2 To 5
> Columns(lColumn).Hidden =3D lColumn > Range("A2").Value
> Next lColumn
> End Sub
> '-------------------------------------------------------------------
>
> See example: http://cjoint.com/?hkljrxXed6
>
> HTH
> --
> AP
>
> <[email protected]> a =E9crit dans le message de news:
> [email protected]...
> > Hello All
> >
> > I have a worksheet which calculates payback percentages for Slot
> > machines, based on how many coins a person plays. Since machines vary
> > in the amount of coins they are allowed to play, I have the sheet set
> > up from one coin up to five coins. On the sheet, if they enter the
> > amount of coins, it should hide the columns that are not needed... eg:
> > If it is 3 coin machine, it will hide the calculation columns for the
> > fourth and fifth coin. If, after that, they change the number to 2
> > coins, it should hide the third coin calculation column, and so on. I
> > tried achieving this with a Case... Select approach, but I have having
> > no luck at all.
> > If anyone has some sort of basic code that hides a column in the same
> > sheet, based on a numeric input in a cell on that same sheet, please
> > help. Thank you for your time.
> >