+ Reply to Thread
Results 1 to 2 of 2

Calender autorange problem on locked worksheet

Hybrid View

  1. #1
    Registered User
    Join Date
    10-04-2008
    Location
    uk
    Posts
    17

    Calender autorange problem on locked worksheet

    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.
    Last edited by simpo067; 04-05-2009 at 07:29 PM.

  2. #2
    Registered User
    Join Date
    10-04-2008
    Location
    uk
    Posts
    17

    Re: Calender autorange problem on locked worksheet

    Ive just removed the offending line and it still seems to work fine so no worries, however if anyone has a more elegant solution it would still be appreciated

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1