Switching views when cell is changed

[The Tamer]

Hi

In a data-entry sheet, I want the views to change depending on what someone selects from a validated list in column E. So, if the user selects "Option A", then the columns that have info relevant to option A are displayed, and all others are hidden. That view will remain until a different option is chosen - causing a different view to be shown.

I have named and saved the views, I just need to know how to write the code... Something like

If cell in column E is changed (via dropdown validation) to "Option A", then goto "View A". 
Else 
If cell in column E is changed (via dropdown validation) to "Option B", then goto "View B".
Else etc etc etc

Many thanks

Damo

[mvidas]

Hi Damo,

Personally I think you'd be better off with a userform for the data entry, and create different 'tabs' for the different views.

But, you could use the idea of what you have above, but link it to the LinkSource cell instead. You could put it in your Worksheet_Change event. Something like

Select Case Range("E1")
  Case 0 '"Option A"
   'code for view a
  Case 1 '"Option B"
   'code for view b
  Case 2 '"Option C"
   'code for view b
  'etc
 End Select

Matt

[The Tamer]

Thanks for your reply Matt,

Unfortunately - your code isn't working on my sheet - I'm getting a type mismatch. Also, does the fact that you refer to "E1" mean that I'm restricted to using that cell? The user inputs his choice on the next available row in column E - and this should then switch views (dependant on what he's input).

I've tried changing the code a few different ways, but i'm getting no joy.

Damo

[mvidas]

I think the reason you got the Mismatch error is because I didn't specify the .Text or .Value after the range object. Also, if you want the last used cell in E, you could try:

Select Case Range("E65536").End(xlUp).Value
Case 0 '"Option A"
Case 1 '"Option B"
Case 2 '"Option C"
'etc 
End Select

See if that works any better. The 0,1,2 is the list index of the dropdown you're using, you could change those to whatever you're actually going to be putting in column E.
Matt

[mark007]

Going back to your original idea Damo assuming your views are all called 'View X' corresponding to options 'Option X' then the following code in the worksheets code module should do the job:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        If Len(Trim$(Target.Value)) > 0 And Target.Column = 5 Then
            ThisWorkbook.CustomViews("View " & Right$(Target.Value, 1)).Show
        End If
    End If
    Target.Offset(1, 0).Select
End Sub

[The Tamer]

Thanks both.

Matt: I still get a type mismatch even using your alterations, not sure what's going on there!

Mark, thanks for your input too. On your suggestion: In fact, the name of the view is identical to the user input. E.g. the user can select "Admin" from a drop-down list, and i have a view called "Admin" that hides irrelevant fields.

Two things on your suggestion: How would your code know to switch views only if a cell in column E has changed (the bottom-most cell in the list)? Also, changing a cell using a drop-down choice does not seem to trigger a Worksheet_Change macro - whereas typing something and hitting enter does. Problem is, they need to choose from a drop-down. Is there a way to get a Worksheet_Change macro to recognise inputs from validation dropdowns?

[mark007]

How would your code know to switch views only if a cell in column E has changed (the bottom-most cell in the list)?

That is what the:

And Target.Column = 5 Then

was for however if you want to make sure it's the bottome cell too then you'll need to adjust it to:

Private Sub Worksheet_Change(ByVal Target As Range) 
    dim lngRow as Long
    If Target.Cells.Count = 1 Then 
lngRow=Me.cells(65536,target.column).End(xlUp).Row
        If Len(Trim$(Target.Value)) > 0 And Target.Column = 5 and target.Row=lngRow Then 
            ThisWorkbook.CustomViews("View " & Right$(Target.Value, 1)).Show 
        End If 
    End If 
    Target.Offset(1, 0).Select 
End Sub

Validation lists should fire the change event. A quick check in my excel showed this - what version of Excel are you using?

[The Tamer]

Mark,

Thanks, I changed:
[VBA]ThisWorkbook.CustomViews("View " & Right$(Target.Value, 1)).Show[/VBA]
to:
[VBA]ThisWorkbook.CustomViews(Target.Value).Show[/VBA]
Which fits what i said about the view name being the same as the input.

But the validation choice doesn't trigger the code - whereas clicking into the formula bar then hitting enter does. I'm using Excel97 on WinNT. Any suggestions? Thx in advance.

Damo

[mark007]

Ah best bet is to ugrade Excel

Is a bug in 97 that it doesn't fire the change evnt I believe. You can bypass with some coding in the calculate event but as you are looking at an entire column this isn't really practical I don't think. Will have a think and get back to you.

[The Tamer]

Ok,

Thanks for your help so far (both).

Assuming In AA1 I can figure out a way to get a formula to return the bottom-most entry in Column E, how would you modify your answer to change the view depending on the result in AA1?

Damo

[mark007]

Use the calcuate event and check the value of cell AA1 i.e.

Private Sub Worksheet_Calculate()
    ThisWorkbook.CustomViews(Me.Range("AA1").Value).Show
End Sub

Incidentally to find the value in the last cell you could use the match function or a UDF such as:

Function LastCell(c As Range)
    LastCell = c.Parent.Cells(65536, c.Column).End(xlUp).Value
End Function

So in AA1 you would then have:

=LastCell(E1)

[The Tamer]

Great,

To get my last entry in E (my list starts in row 206 btw) I used a formula:

=INDEX(E206:E65536,MATCH("zzzzzzz",E206:E65536,1))

But it's good to know how to do in vba! Cheers!

Trouble is, the code doesn't seem to be doing anything. The formula in AA1 is working properly. I've defined a view "Admin", and I select "Admin" from my drop-down box -the formula is changing - but nothing's happening to the view! This doesn't work even when I type the entry.

Darn!!!

[mark007]

Hmm, workbook works for me...

[The Tamer]

What, mine?

OK, Perhaps there is another way around this. Matt suggested a userform. If you look at the posted sheet, you'll see that the list of options in F207 will change depending on what is put in E207. Will I be able to accomplish the same effect with a userform - that is - only ask for details that are relavent to the primary input choice?

I have no idea how to create user forms - though some have done some for me in the past which I may tear apart to see what's going on.

[mark007]

Just got someone to try it on 97 and it works fine.

You haven't got calculation set to manual have you?

[The Tamer]

OK,

I checked - and calculation was set to automatic. I shut everything down and restarted - and it worked!.

So thanks both for your help on this - it's really much appreciated.

Damo