PDA

View Full Version : [SOLVED] is sheet selection by variable possible?



MINCUS1308
07-02-2014, 06:35 AM
In my workbook I have a table similar to the one found below; in that, the last column of data actually represents a worksheet name that is validated by a drop down list.


NAME
NUMBER
CLASS
RANK
LOCATION


JOSH
52
AMANDA
4
1


ANDREW
36
KELLY
3
7


SCOTT
96
KELLY
1
10


BILL
101
AMANDA
2
3



And I am using the following code as -a kind of - map location service.


Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

'CHECK IF DOUBLE CLICK EVENT WAS IN TABLE ROW
If (Target.Row > 7) And (Target.Row < 3333) Then

'COLUMN OFFSET DISTANCE FROM CELL A1 FOR LOCATION DATA
COLMUNX = 80
COLUMNY = 81
FLOORCOLUMN = 82
'GRABBING THE FLOOR & COORDINATES TO HIGHLIGHT
VALUEX = Range("A1").Offset(Target.Row - 1, COLMUNX).Value
VALUEY = Range("A1").Offset(Target.Row - 1, COLUMNY).Value
THEFLOOR = Range("A1").Offset(Target.Row - 1, FLOORCOLUMN).Value

'CALLING THE CORRECT SHEET (MAP) INTO FOCUS
Select Case THEFLOOR
Case "1"
Sheets("1").Select
Case "2"
Sheets("2").Select
Case "3"
Sheets("3").Select
Case "4"
Sheets("4").Select
Case "5"
Sheets("5").Select
Case "6"
Sheets("6").Select
Case "7"
Sheets("7").Select
End Select

'SELECTING MAP CELL AND GRABBING CURRENT COLOR
Range("A1").Offset(VALUEX - 1, VALUEY - 1).Select
MYCOLOR = Selection.Interior.Color

'DETERMINING WHICH COLOR TO BLINK (BLACK OR GREEN)
If MYCOLOR = 65280 Then
BLINKCOLOR = RGB(0, 0, 0) 'BLACK
Else
BLINKCOLOR = 65280 ' GREEN
End If

'BLINK SEQUENCE (12 SECONDS)
For I = 0 To 5
Selection.Interior.Color = BLINKCOLOR
Application.Wait (Now + TimeSerial(0, 0, 1))
Selection.Interior.Color = MYCOLOR
Application.Wait (Now + TimeSerial(0, 0, 1))
Next I

'RESET FOCUS TO LIST WORKSHEET
Sheets("LIST").Select

End If

End Sub




The problem is I have some 100 possible worksheets that could be potentially called from my table and I dont want to hard code a Select Case for each of them. I know there must be an easier way. I have considered the following:


Sub TEST()
' I KNOW THIS WORKS AS A SOLUTION
Set X = Sheet11
X.Select

'BUT I REALLY THINK I NEED SOMETHING MORE LIKE THE FOLLOWING
' SET X = Range("A1").Offset(Target.Row - 1, FLOORCOLUMN).Value
' X.SELECT
End Sub



Any help would be much appreciated.

p45cal
07-02-2014, 09:38 AM
If the values in Range("A1").Offset(Target.Row - 1, FLOORCOLUMN) are exactly the same as the names of the sheets then you don't need Select Case at all, just use:
Sheets(THEFLOOR).select

As an aside, you don't need to use offsets here:

COLMUNX = 80
COLUMNY = 81
FLOORCOLUMN = 82
'GRABBING THE FLOOR & COORDINATES TO HIGHLIGHT
VALUEX = Range("A1").Offset(Target.Row - 1, COLMUNX).Value
VALUEY = Range("A1").Offset(Target.Row - 1, COLUMNY).Value
THEFLOOR = Range("A1").Offset(Target.Row - 1, FLOORCOLUMN).Value
simpler:

COLMUNX = 81 'was 80
COLUMNY = 82 'was 81
FLOORCOLUMN = 83 ' was 82
'GRABBING THE FLOOR & COORDINATES TO HIGHLIGHT
VALUEX = cells(Target.Row, COLMUNX).Value
VALUEY = cells(Target.Row, COLUMNY).Value
THEFLOOR = Cells(Target.Row, FLOORCOLUMN).Value


Then later, instead of:
Range("A1").Offset(VALUEX - 1, VALUEY - 1).Select
use:
Cells(VALUEX, VALUEY).Select

Another thing; you've put this in the Thisworkbook code-module as Workbook_SheetBeforeDoubleClick, but I suspect you dont want other sheets to respond in the same way to double-clicking, so I'd hazard a guess that the code might be better off in the LIST sheet's code module Worksheet_BeforeDoubleClick event handler, with a little tweak of:
Range("A1").Offset(VALUEX - 1, VALUEY - 1).Select
to:
Activesheet.Range("A1").Offset(VALUEX - 1, VALUEY - 1).Select, or if you use my suggestion regarding dispensing with the offsets change:
Cells(VALUEX, VALUEY).Select
to:
Activesheet.Cells(VALUEX, VALUEY).Select

MINCUS1308
07-02-2014, 12:52 PM
Thank you so much for taking the time to put together such a thorough reply.
The changes you suggested are fantastic!

Below is the working code for future references:


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


If (Target.Row > 7) And (Target.Row < 33) Then

'COLUMN OFFSET DISTANCE FROM CELL A1 FOR LOCATION DATA
COLMUNX = 9
COLUMNY = 10
FLOORCOLUMN = 11

'GRABBING THE FLOOR & COORDINATES TO HIGHLIGHT
VALUEX = Cells(Target.Row, COLMUNX).Value
VALUEY = Cells(Target.Row, COLUMNY).Value
THEFLOOR = Cells(Target.Row, FLOORCOLUMN).Value

'SELECTING MAP CELL AND GRABBING CURRENT COLOR
Sheets(THEFLOOR).Select
ActiveSheet.Cells(VALUEX, VALUEY).Select
MYCOLOR = Selection.Interior.Color

'DETERMINING WHICH COLOR TO BLINK (BLACK OR GREEN)
If MYCOLOR = 65280 Then
BLINKCOLOR = RGB(0, 0, 0) 'BLACK
Else
BLINKCOLOR = 65280 'GREEN
End If

'BLINK SEQUENCE (8 SECONDS)
For I = 0 To 3
Selection.Interior.Color = BLINKCOLOR
Application.Wait (Now + TimeSerial(0, 0, 1))
Selection.Interior.Color = MYCOLOR
Application.Wait (Now + TimeSerial(0, 0, 1))
Next I

'RESET TO LIST WORKSHEET
Sheets("LIST").Select

End If

End Sub