Juriemagic
04-12-2016, 02:20 AM
Hi good people!,
This is a problem I am sooo tired of struggling with, please help me with this:
This code:
Private Sub Worksheet_Activate()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
If WS.Name <> ActiveSheet.Name Then WS.Visible = xlSheetVeryHidden
Next WS
Sheets("LOG").Unprotect
Range("LOG").Select
ActiveWindow.Zoom = True
Range("A1").Select
ActiveWindow.SmallScroll toleft:=10
Application.DisplayFormulaBar = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False
Range("C4").Select
Sheets("LOG").Protect
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Sheets("LOG").Unprotect
Dim rCell As Range
Dim rChange As Range
Set rChange = Intersect(Target, Range("C:C"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
rCell.Offset(0, 1).Value = Environ$("UserName")
rCell.Offset(0, 2).Value = Now
Else
'do nothing
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Sheets("LOG").Protect
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Private Sub Worksheet_Deactivate()
Sheets("LOG").Unprotect
End Sub
Private Sub Worksheet_Activate()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
If WS.Name <> ActiveSheet.Name Then WS.Visible = xlSheetVeryHidden
Next WS
Sheets("LOG").Unprotect
Range("LOG").Select
ActiveWindow.Zoom = True
Range("A1").Select
ActiveWindow.SmallScroll toleft:=10
Application.DisplayFormulaBar = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False
Range("C4").Select
Sheets("LOG").Protect
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Sheets("LOG").Unprotect
Dim rCell As Range
Dim rChange As Range
Set rChange = Intersect(Target, Range("C:C"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
rCell.Offset(0, 1).Value = Environ$("UserName")
rCell.Offset(0, 2).Value = Now
Else
'do nothing
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Sheets("LOG").Protect
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Private Sub Worksheet_Deactivate()
Sheets("LOG").Unprotect
End Sub
The above code resides in the LOG sheet.
Then I have this code which resides in a BUTTON on the UPDATE ROOM sheet:
Sub LogChanges()
'
' LogChanges Macro
'
'
If ActiveSheet.Range("A100").Value = 1 Then Exit Sub
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
On Error GoTo a:
Set copySheet = Worksheets("Update Room")
Set pasteSheet = Worksheets("LOG")
copySheet.Unprotect
copySheet.Range("E3").Copy
With pasteSheet
.Unprotect
.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
.Range("C4:E1004").Select
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("E4:E1004"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortTextAsNumbers
With pasteSheet.Sort
.SetRange Range("C4:E1004")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
copySheet.Select
copySheet.Unprotect
Range("A100").Value = 1
ActiveSheet.Shapes.Range(Array("Button 23")).Visible = False
Range("E3:F3").Locked = False
Range("C3:D3").Locked = False
pasteSheet.Activate
Range("A1").Select
copySheet.Protect
pasteSheet.Protect
a:
Exit Sub
End Sub
When certain changes are made to the UPDATE ROOM sheet, button 23 becomes visible. On clicking this button, the code executes to copy and paste to the LOG sheet. What happens is that I get "Error 1004, Select method.....". When debugging I see the LOG code with yellow highlight on
Range("LOG").Select.
NOW, there is another button on the UPDATE ROOM sheet which has the caption "LOG". This is just a normal navigation button to make the LOG sheet visible and xlveryhidden the UPDATE ROOM sheet. When navigating like this, no errors come up. The error ONLY comes up when I need to UPDATE the LOG sheet, using the "Log Changes" macro.
All and any help will be accepted with great humility and admiration, Thank you all very much!
This is a problem I am sooo tired of struggling with, please help me with this:
This code:
Private Sub Worksheet_Activate()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
If WS.Name <> ActiveSheet.Name Then WS.Visible = xlSheetVeryHidden
Next WS
Sheets("LOG").Unprotect
Range("LOG").Select
ActiveWindow.Zoom = True
Range("A1").Select
ActiveWindow.SmallScroll toleft:=10
Application.DisplayFormulaBar = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False
Range("C4").Select
Sheets("LOG").Protect
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Sheets("LOG").Unprotect
Dim rCell As Range
Dim rChange As Range
Set rChange = Intersect(Target, Range("C:C"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
rCell.Offset(0, 1).Value = Environ$("UserName")
rCell.Offset(0, 2).Value = Now
Else
'do nothing
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Sheets("LOG").Protect
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Private Sub Worksheet_Deactivate()
Sheets("LOG").Unprotect
End Sub
Private Sub Worksheet_Activate()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
If WS.Name <> ActiveSheet.Name Then WS.Visible = xlSheetVeryHidden
Next WS
Sheets("LOG").Unprotect
Range("LOG").Select
ActiveWindow.Zoom = True
Range("A1").Select
ActiveWindow.SmallScroll toleft:=10
Application.DisplayFormulaBar = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False
Range("C4").Select
Sheets("LOG").Protect
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Sheets("LOG").Unprotect
Dim rCell As Range
Dim rChange As Range
Set rChange = Intersect(Target, Range("C:C"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
rCell.Offset(0, 1).Value = Environ$("UserName")
rCell.Offset(0, 2).Value = Now
Else
'do nothing
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Sheets("LOG").Protect
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Private Sub Worksheet_Deactivate()
Sheets("LOG").Unprotect
End Sub
The above code resides in the LOG sheet.
Then I have this code which resides in a BUTTON on the UPDATE ROOM sheet:
Sub LogChanges()
'
' LogChanges Macro
'
'
If ActiveSheet.Range("A100").Value = 1 Then Exit Sub
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
On Error GoTo a:
Set copySheet = Worksheets("Update Room")
Set pasteSheet = Worksheets("LOG")
copySheet.Unprotect
copySheet.Range("E3").Copy
With pasteSheet
.Unprotect
.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
.Range("C4:E1004").Select
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("E4:E1004"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortTextAsNumbers
With pasteSheet.Sort
.SetRange Range("C4:E1004")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
copySheet.Select
copySheet.Unprotect
Range("A100").Value = 1
ActiveSheet.Shapes.Range(Array("Button 23")).Visible = False
Range("E3:F3").Locked = False
Range("C3:D3").Locked = False
pasteSheet.Activate
Range("A1").Select
copySheet.Protect
pasteSheet.Protect
a:
Exit Sub
End Sub
When certain changes are made to the UPDATE ROOM sheet, button 23 becomes visible. On clicking this button, the code executes to copy and paste to the LOG sheet. What happens is that I get "Error 1004, Select method.....". When debugging I see the LOG code with yellow highlight on
Range("LOG").Select.
NOW, there is another button on the UPDATE ROOM sheet which has the caption "LOG". This is just a normal navigation button to make the LOG sheet visible and xlveryhidden the UPDATE ROOM sheet. When navigating like this, no errors come up. The error ONLY comes up when I need to UPDATE the LOG sheet, using the "Log Changes" macro.
All and any help will be accepted with great humility and admiration, Thank you all very much!