Simple VBA code, anyone? [SOLVED]

[exceljockey]

Hi


I am pretty new to writing code. I am trying to achieve something on my dashboard.

The Logic goes as follows: If Cells B4,C4, or D4 are selected, then zoom in 120%. If cells T3 or T15 are selected, then zoom in 120% AND scroll right 15 columns. If cells B17 or B30 are selected, then zoom in 120% AND scroll down 15 rows. If ANY OTHER cells are selected, then zoom out to 70% & scroll 1 column, and 1 row (To reset position of the dashboard).


The code I have made is:

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("B4,C4,D4,E4,B17,B30,T3,T15")) Is Nothing Then
ActiveWindow.Zoom = 70
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1

Else
ActiveWindow.Zoom = 120

End If

End Sub

[/pre]

Now This works for zooming in, but I don't know how to split the code up so that it does separate things for separate cells selected (as described in the logic).


Any help, or pointers, would be very much appreciated!


Thanks,

EJ

 

[Hui]

EJ


I assume that you want to do something like:

[pre]

If Intersect(Target, Range("B4,C4,D4,E4,B17,B30,T3,T15")) Is Nothing Then
ActiveWindow.Zoom = 70
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Select Case target.address
case $B$4
'do something
case $C$4
'do something
'etc
End Select

Else

[/pre]

 

[exceljockey]

Thanks for the reply, so basically the reason behind all of this is because I have some drop down lists on my dashboard and as you know, the tiny font in the drop down lists is not user friendly. My aim is to zoom into the cell when I click the cell, but also to center the screen while doing it, the only way I can think of doing this is by using scrollcolumn and scrollrow (I am very new to VBA); So my plan is that when cells "T.." are selected then the screen to scroll over to the right, and when cells "B30" are selected (down at the bottom of the dashboard) then the page to scroll down so that the cells are centralised.


Im going to use this code that you have written with the Case Target; will let you know how it goes.


Regards

EJ

 

[exceljockey]

Right So I have tried to play about with what you just replied with, I have written this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

[pre]

If Intersect(Target, Range("B4,C4,D4,E4,B17,B30,T3,T15")) Is Nothing Then
ActiveWindow.Zoom = 70
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Select Case Target.Address
Case T3
ActiveWindow.Zoom = 120
ActiveWindow.ScrollColumn = 15

End Select

Else
ActiveWindow.Zoom = 120

End If

End Sub

[/pre]

This doesn't do what I need it to, it just does the same as it did before which is just zoom in 120%.

 

[NARAYANK991]

Hi ,

Change the placement of the Select Case statement as follows :

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B4,C4,D4,E4,B17,B30,T3,T15")) Is Nothing Then
ActiveWindow.Zoom = 70
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Else
Select Case Target.Address
Case "$T$3", "$T$15"
ActiveWindow.Zoom = 120
ActiveWindow.ScrollColumn = 15
Case "$B$4", "$C$4", "$D$4", "$E$4"
.
.
Case "$B$17","$B$30"
.
.
End Select
End If
End Sub

[/pre]

 

[exceljockey]

Hi


I have tried doing this, it didn't work. In fact all it did was make the entire thing not work so no zoom works at all with this code, but I don't see why since the code makes sense...

Maybe I should try a different solution to this problem like a combo box or something.

 

[NARAYANK991]

Hi ,


It worked for me. Probably if you upload your file , we can check.


Narayan

 

[exceljockey]

Unfortunately I cannot do that - private information and what not...

Ill try it on a blank excel sheet and see what happens!

 

[Hui]

EJ


Replace all the fields with 1's or A's etc

Multiply values by a Random number

Simplify to 10 records etc to post a sample file


Once we supply a solution, you can rescale it to suit your application

 

[exceljockey]

Hi Guys


Thanks for all the help, I have managed to get it to work, It turns out Naranyank was correct!


You have helped me greatly, I really appreciate it.

In future I will take your advice Hui and upload a spreadsheet after changing the fields and such.


Thanks again