PDA

View Full Version : [SOLVED] Switching views when cell is changed



The Tamer
02-03-2005, 08:42 AM
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
02-03-2005, 10:13 AM
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
02-07-2005, 01:37 AM
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
02-07-2005, 07:08 AM
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
02-08-2005, 03:17 AM
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
02-08-2005, 04:00 AM
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? :think:

mark007
02-08-2005, 04:29 AM
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
02-08-2005, 04:41 AM
Mark,

Thanks, I changed:
ThisWorkbook.CustomViews("View " & Right$(Target.Value, 1)).Show
to:
ThisWorkbook.CustomViews(Target.Value).Show
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
02-08-2005, 05:19 AM
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
02-08-2005, 05:46 AM
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
02-08-2005, 06:05 AM
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
02-08-2005, 06:29 AM
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
02-08-2005, 06:34 AM
Hmm, workbook works for me...

The Tamer
02-08-2005, 06:36 AM
What, mine? :aw

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
02-08-2005, 07:12 AM
Just got someone to try it on 97 and it works fine.

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

:)

The Tamer
02-08-2005, 08:18 AM
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