Msgbox answer to amend existing code

Graechel

New Member
Joined
Dec 22, 2020
Messages
5
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.

VBA Code:
Sub Locker()
ActiveSheet.Unprotect
Columns("E").Select
    Selection.Locked = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ThisWorkbook.Save
   End
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,485
Office Version
  1. 2019
Platform
  1. Windows
Hi,
welcome to forum

see if this update to your code does what you want

Rich (BB code):
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
 
Solution

Graechel

New Member
Joined
Dec 22, 2020
Messages
5
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Thanks Dave. Worked a treat. Now go off and enjoy your Christmas. Graeme
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,485
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,753
Messages
5,626,665
Members
416,199
Latest member
Gautamsunil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top