Gents
I've a small problem. I'm using the following code to conditionally lock parts of my worksheet dependant on a lock/unlock statement.
I also want to use a popup calendar which i found on this site (apologies for not recalling whose)
The problem appears to be that the lock code prevents the calendar from autofitting thus generating a "autofit method of range class" error/
What I'd like to do is either get the lock/unlock code to accept the date input or alter the calendar code to prevent the autofit error
first the lock/unlock code
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim WS As Worksheet
Set WS = Target.Parent
WS.Unprotect
For Each Cell In Target
If Cell.Column = 21 Then
If Cell = "Lock" Then
Range(Cells(Cell.Row, 1), Cells(Cell.Row, 10)).Locked = True
ElseIf Cell = "Unlock" Then
Range(Cells(Cell.Row, 1), Cells(Cell.Row, 10)).Locked = False
End If
End If
Next Cell
WS.Protect
End Sub
Now the calendar code, the point that the debug stops is in red.
Option Explicit
'use a class module to create a collection of commandbuttons
Public WithEvents CmdBtnGroup As MSForms.CommandButton
Sub CmdBtnGroup_Click()
If Left(CmdBtnGroup.Name, 1) <> "D" Then Exit Sub
If Month(CDate(CmdBtnGroup.Tag)) <> frmCalendar.CB_Mth.ListIndex + 1 Then
Select Case _
MsgBox("The selected date is not in the currently selected month." _
& vbNewLine & "Continue?", _
vbYesNo Or vbExclamation Or vbDefaultButton1, "Date check")
Case vbYes
If g_bForm Then
GoTo on_Form
Else: GoTo addDate
End If
Case vbNo
Exit Sub
End Select
Else:
If g_bForm Then
GoTo on_Form
Else: GoTo addDate
End If
addDate:
With ActiveCell
.Value = CDate(CmdBtnGroup.Tag)
.EntireColumn.AutoFit
End With
with frmCalendar
.lblWeekNum.Caption = "Week number: " & VBAWeekNum(CmdBtnGroup.Tag, 2)
.lblISOweekNum.Caption = "ISO Week number: " & IsoWeekNumber(CmdBtnGroup.Tag)
.lblDayNum.Caption = "Day number: " & DayOfYear(CDate(CmdBtnGroup.Tag))
.lblZodiac.Caption = "Zodiac sign: " & ZodiacSign(CDate(CmdBtnGroup.Tag))
.Height = frmHeight2
End With
GoTo chg_month
on_Form:
g_sDate = CmdBtnGroup.Tag
chg_month:
With frmCalendar.CB_Mth
.ListIndex = Month(CmdBtnGroup.Tag) - 1
End With
End If
'unload the form after adding date
' Unload frmCalendar
End Sub
Any help is appreciated, I'm happy that the conflict is with the lock/unlock as the calendar works lovely without it.
Bookmarks