Hiding Column based on If Condition

[asr]

Hi,

I would like to know if it is possible to hide Column/Row based on I
condition. (If the cell A1 has a particular value then hide ColumnB).

Incase this isn't possible, can you help me with this problem -

I'm having a drop down list with Jan-Dec in Cell A1. In Cells B2:G10,
need to input a value corresponding to each of the months. This valu
I'm retreiving and using in a different location using If Condition
The problem is that incase I change the month to Feb, the value
inputted for Jan remains the same throughout. Any change made for Fe
gets reflected throughout. I'm not able to tie Jan-Dec to the Cel
values in B2"G10.

How to retain the values, specific for each of the months. The inputte
data should remain the same, even if I change the value in A1

The simple way out would be to have Jan-Dec in separate cells but
feel that this would make the data entry screen big and complicated fo
the end user. In case no month is selected a default value is choosen.


Is there any other way out to input the data and retrive it elsewhere

 

[Rajni Ravindran]

Hi!



I can think of a solution like this:

1. On the same sheet where you are trying to input data, mark 12 blocks of
cells, 1 for each month.

2. Hide all the bloacks at the outset

3. Ask the user to choose the month from the drop down in cell1

4. Unhide the specific area (rows or columns) depending on how you have
organised the data corresponding to the chosen month

5. So you would not have to store the data elsewhere, but if you wish to do
that, you could do that too.



You will have to write some code in Visual Basic to accomplish this.



Let me know if this works/ does not work for you



There might be more elegant and easier solutions, but just thought I'd give
you a start since I hadn't seen any responses to this post yet.



Regards,

Rajni



Ps: this is my first attempt at posting to a newsgroup. So in case I have
made any mistakes, please feel free to point out



Rajni

I would like to know if it is possible to hide Column/Row based on If

condition. (If the cell A1 has a particular value then hide ColumnB).

Incase this isn't possible, can you help me with this problem -

I'm having a drop down list with Jan-Dec in Cell A1. In Cells B2:G10,

I need to input a value corresponding to each of the months. This

value I'm retreiving and using in a different location using If

Condition. The problem is that incase I change the month to Feb, the

values inputted for Jan remains the same throughout. Any change made

for Feb gets reflected throughout. I'm not able to tie Jan-Dec to the

Cell values in B2"G10.

How to retain the values, specific for each of the months. The

inputted data should remain the same, even if I change the value in A1

The simple way out would be to have Jan-Dec in separate cells but I

feel that this would make the data entry screen big and complicated

for the end user. In case no month is selected a default value is



Is there any other way out to input the data and retrive it elsewhere?





asr's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=23751

 

[Dave Peterson]

You could use a worksheet_change event (if you're typing the value into A1):

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this into the codewindow:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count > 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("a1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
.Range("c1").EntireColumn.Hidden = CBool(LCase(.Value) = "hide")
End With

errHandler:
Application.EnableEvents = True

End Sub

Back to excel and type Hide in A1 and then type something else into A1. Watch
what happens to column C.