Restrict OpenEvent code to one column

[stuartgb100]

Can I amend the following code such that ONLY when a cell in column B is selected,
formatting that cell is permitted ?

Private Sub Workbook_Open()
    Sheets("Sheet1").Protect Password:="123", _
        DrawingObjects:=True, Contents:=True, _
        Scenarios:=True, AllowFormattingCells:=True, _
        AllowFormattingColumns:=True, AllowFormattingRows:=True, _
        AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True, UserInterfaceOnly:=True
End Sub

I particularly wish to allow changes to:
font colour, fill colour, italic, bold and underline.

Thanks.

[Greg M]

Hi there,

Try inserting the following code into the VBA CodeModule of the worksheet you wish to edit, and see if it does what you want:

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Const iENABLED_COLUMN_NO    As Integer = 2
    Const sTYPE_RANGE           As String = "Range"
    Const sPASSWORD             As String = "123"

    If TypeName(Selection) = sTYPE_RANGE Then

        If Selection.Columns.Count = 1 Then

            If Selection.Column = iENABLED_COLUMN_NO Then
                  Me.Unprotect Password:=sPASSWORD
            Else: Me.Protect Password:=sPASSWORD
            End If

        End If

    End If

End Sub

The highlighted value corresponds to Column B - this can be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[stuartgb100]

Hi Greg, thanks.

I had thought of something like that, but worried about handing control back to the sheet with protection removed.

I guess now they'll be able to do stuff as long as they don't select another cell ?

Is there a way (after your code runs) to limit their freedom with the selected cell
to just:
font colour, fill colour, italic, bold and underline ?

Many thanks.

[Greg M]

Hi again,

Take a look at the attached workbook - it allows only formatting of the font characteristics of cells in Column B.

The following code is inserted in a standard VBA CodeModule:

Option Explicit


Sub EditCells()

    Const iENABLED_COLUMN_NO    As Integer = 2
    Const sSHEET_NAME           As String = "Sheet1"
    Const sTYPE_RANGE           As String = "Range"
    Const sPASSWORD             As String = "123"

    Dim bOkToProceed            As Boolean
    Dim wks                     As Worksheet

    Set wks = ThisWorkbook.Worksheets(sSHEET_NAME)

    If TypeName(Selection) = sTYPE_RANGE Then

        If Selection.Columns.Count = 1 Then

            If Selection.Column = iENABLED_COLUMN_NO Then

                      bOkToProceed = True

                      wks.Unprotect Password:=sPASSWORD
                          Application.Dialogs(xlDialogActiveCellFont).Show
                      wks.Protect Password:=sPASSWORD

                Else: bOkToProceed = False

                End If

          Else: bOkToProceed = False

          End If

    Else: bOkToProceed = False

    End If

    If bOkToProceed = False Then
        MsgBox "This facility is available only for cells in Column " & _
                iENABLED_COLUMN_NO, vbExclamation
    End If

End Sub

It's might be possible to allow the cell background colour to be changed also, but that would definitely make life more complicated.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[stuartgb100]

Greg,

Many thanks for the help, and the workbook - at my level, never even knew about application.dialogs !

I think I'll keep the workbook on one side for future reference, and work with the code example you gave me.
See where that leads.

Many thanks again.

Regards.

[Greg M]

Hi again,

Many thanks for your feedback - glad I was able to help (even if not 100% )

Best regards,

Greg M