Msgbox answer to amend existing code

[Graechel]

Am absolute novice but self training.
The following code does task intended but having trouble writing macro to use msgbox reply.

Macro to ask "Is all Data ready and correct?" If Yes then ask,
" Please enter current Column Number then press OK"
"Your data will be locked and can not be changed"

A No or Cancel will end Macro and return to sheet1 (Master) as this code currently does.
Macro will be manually activated by a Button.

Then this is where I have difficulty.
OK will use entered data to modify ("E") portion of third line of code below and run.

Sub Locker()
ActiveSheet.Unprotect
Columns("E").Select
    Selection.Locked = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ThisWorkbook.Save
   End
End Sub

 

[dmt32]

Hi,
welcome to forum

see if this update to your code does what you want

Sub Locker()
    Dim Response    As VbMsgBoxResult
    Dim ColNo       As Variant
    Dim prompt      As String, Default As String
    Dim wsMaster    As Worksheet
    
    Const LockPassword As String = "mypassword"
    
    Set wsMaster = Worksheets("Sheet1")
    
    prompt = "Please enter Column Number Or Letter Then press OK" & Chr(10) & _
             "Your data will be locked And cannot be changed"
    
    Response = MsgBox("Is all Data ready And correct?", 36, "Confirm")
    If Response = vbNo Then GoTo exitsub
    
    'optional - display the active column
    'Default = ActiveCell.Column
    
    Do
        ColNo = InputBox(prompt, "Enter Column Number", Default)
        'cancel pressed
        If StrPtr(ColNo) = 0 Then GoTo exitsub
        'report inavlid entry
        Default = "Invalid Entry - Try Again"
        'loop until valid alpha or numeric data entered
    Loop Until Not ColNo Like "*[!A-Za-z]*" Or Not ColNo Like "*[!0-9]*"
    'if numeric coerce string to number
    If IsNumeric(ColNo) Then ColNo = Val(ColNo)
    
    ActiveSheet.Unprotect LockPassword
    Columns(ColNo).Locked = True
    ActiveSheet.Protect Password:=LockPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True
    ThisWorkbook.Save
    
exitsub:
    wsMaster.Select
End Sub

Code should allow users to reference columns either by it's letter name (alpha) or a numeric value

I have also included password shown in bold - change as required.

Hope Helpful

Dave

 

[Graechel]

Thanks Dave. Worked a treat. Now go off and enjoy your Christmas. Graeme

 

[dmt32]

Thanks Dave. Worked a treat. Now go off and enjoy your Christmas. Graeme

I did enjoy xmas thanks

Glad code does what you want & Appreciate feedback

Dave