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.
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.