Hi, BKGirl!
Give a look at this file:
https://dl.dropboxusercontent.com/u...11314 for et (for BKGirl at chandoo.org).xlsm
It uses 4 dynamic named ranges for easy and flexible reference as follows:
ProjectTable: 1st worksheet data range
=DESREF('2014 Projects'!$A$4;;;CONTARA('2014 Projects'!$A:$A)-2;CONTARA('2014 Projects'!$3:$3)) -----> in english: =OFFSET('2014 Projects'!$A$4,,,COUNTA('2014 Projects'!$A:$A)-2,COUNTA('2014 Projects'!$3:$3))
ProjectList: 1st column of previous
=DESREF(ProjectTable;;0;;1) -----> in english: =OFFSET(ProjectTable,,0,,1)
DetailTable: 2nd WS data range
=DESREF('Detailed Project Master List'!$A$2;;;CONTARA('Detailed Project Master List'!$A:$A)-1;CONTARA('Detailed Project Master List'!$1:$1)) -----> in english: =OFFSET('Detailed Project Master List'!$A$2,,,COUNTA('Detailed Project Master List'!$A:$A)-1,COUNTA('Detailed Project Master List'!$1:$1))
DetailList: 5th column of previous
=DESREF(DetailTable;;4;;1) -----> in english: =OFFSET(DetailTable,,4,,1)
In 1st worksheet you have 2 command buttons: Mode and Method.
Mode serves for switching between edit mode and link mode, otherwise if it's always linking whenever you click on column A it'd be a bit difficult to edit its values.
Method serves for switching between filter method and color method. Yes, you get a 2 by 1 (as I'd get the same bonus in my six-pack
).
When the workbook is opened the default values are edit and filter, but you can change them in the constants section of the below code.
In 2nd worksheet you have 1 command button to undo what done and return.
This is the code for workbook class module:
Code:
Option Explicit
Private Sub Workbook_Open()
SetMode pgksModeEdit
SetMethod pgksMethodFilter
End Sub
This is the code for 1st WS class module:
Code:
Option Explicit
Private Sub cmdMode_Click()
ChangeMode
End Sub
Private Sub cmdMethod_Click()
ChangeMethod
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' constants
Const kiColumn = 1
Const kiRow = 4
' declarations
' start
' edit, not col A, wrong row, empty cell, then quit
If gsMode = pgksModeEdit Then Exit Sub
If Application.Intersect(Target, Columns(kiColumn)) Is Nothing Then Exit Sub
With Target
If .Row < kiRow Or .Cells.Count > 1 Then Exit Sub
If .Value = "" Then Exit Sub
gsProject = .Value
End With
' process
FilterOrColor
' end
End Sub
This is the code for 2nd WS class module:
Code:
Option Explicit
Private Sub cmdBack_Click()
UnFilterOrDisColor
End Sub
This is the code for the standard module (where all happens):
Code:
Option Explicit
' public constants
Public Const pgksModeEdit = "Edit"
Public Const pgksModeLink = "Link"
Public Const pgksMethodFilter = "Filter"
Public Const pgksMethodColor = "Color"
' public declarations
Public gsMode As String, gsMethod As String, gsProject As String
' global constants
' ws & rng
Global Const gksWSProject = "2014 Projects"
Global Const gksRngProject = "ProjectList"
Global Const gksWSDetail = "Detailed Project Master List"
Global Const gksRngDetail = "DetailList"
' mode & method
Global Const gksModeDefault = pgksModeEdit
Global Const gksMethodDefault = pgksMethodFilter
Sub SetMode(psMode As String)
' constants
Const ksMode = "Mode "
' declarations
Dim A As String
' start
Select Case psMode
Case pgksModeEdit, pgksModeLink
A = psMode
Case Else
A = gksModeDefault
MsgBox "Wrong mode parameter. Default used.", _
vbApplicationModal + vbCritical + vbOKOnly, "Warning"
End Select
' process
gsMode = A
Worksheets(gksWSProject).cmdMode.Caption = ksMode & gsMode
' end
End Sub
Sub SetMethod(psMethod As String)
' constants
Const ksMethod = "Method "
' declarations
Dim A As String
' start
Select Case psMethod
Case pgksMethodFilter, pgksMethodColor
A = psMethod
Case Else
A = gksMethodDefault
MsgBox "Wrong method parameter. Default used.", _
vbApplicationModal + vbCritical + vbOKOnly, "Warning"
End Select
' process
gsMethod = A
Worksheets(gksWSProject).cmdMethod.Caption = ksMethod & gsMethod
' end
End Sub
Sub ChangeMode()
' constants
' declarations
' start
' process
Select Case gsMode
Case pgksModeEdit
SetMode pgksModeLink
Case pgksModeLink
SetMode pgksModeEdit
Case Else
SetMode gksModeDefault
End Select
' end
End Sub
Sub ChangeMethod()
' constants
' declarations
' start
' process
Select Case gsMethod
Case pgksMethodFilter
SetMethod pgksMethodColor
Case pgksMethodColor
SetMethod pgksMethodFilter
Case Else
SetMethod gksMethodDefault
End Select
' end
End Sub
Sub FilterOrColor()
' constants
Const klColor = &HFFFF00
' declarations
Dim I As Long
' start
If gsProject = "" Then MsgBox "wtf": Stop
' process
With Worksheets(gksWSDetail)
.Activate
With .Range(gksRngDetail)
Select Case gsMethod
Case pgksMethodFilter
.AutoFilter .Column, gsProject
Case pgksMethodColor
For I = 1 To .Cells.Count
If .Cells(I, 1).Value = gsProject Then
.Cells(I, 1).Interior.Color = klColor
End If
Next I
End Select
.Offset(-1, 0).Cells(1, 1).Select
On Error Resume Next
.Find(gsProject, , xlValues, xlWhole, , True).Select
If Err.Number <> 0 Then
Err.Clear
MsgBox "No entries in master list for the selected project", _
vbApplicationModal + vbCritical + vbOKOnly, "Warning"
End If
On Error GoTo 0
End With
End With
' end
End Sub
Sub UnFilterOrDisColor()
' constants
' declarations
' start
' process
With Worksheets(gksWSDetail)
.Activate
With .Range(gksRngDetail)
Select Case gsMethod
Case pgksMethodFilter
If .Parent.AutoFilter.FilterMode Then .Parent.ShowAllData
Case pgksMethodColor
.Cells.Interior.ColorIndex = xlNone
End Select
.Offset(-1, 0).Cells(1, 1).Select
End With
End With
' end
Worksheets(gksWSProject).Activate
End Sub
Just advise if any issue.
Regards!
