Move from one cell to another

Folks,
In my worksheet I need to disable cell O2 when the workbook is activated.
In cell M3 I need to:
1. Check that the value entered is between 1 and 12
2. If true then I need to enable cell O2 and move to cell O2 to enter my formula.
Frank FreeseAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harry LeeCommented:
fh_freese,

You can put these VBA into the workbook.

Alt-11 to go to VBA Editor. Ctrl-R to open Project Explorer.

In the project explorer, find the workbook you are working on, and double click on ThisWorkbook.

Copy and paste the following vba codes in there.

This will protect the all the sheets in the workbook except M3
Private Sub Workbook_Open()
Dim WS As Worksheet
For Each WS In Worksheets
With WS.Range("m3")
    .Locked = False
    .FormulaHidden = False
End With
    WS.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Next
End Sub

Open in new window

Upon change of M3, and if the entered value is between 1 and 12, unprotect sheet and jump to O2.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$M$3" Then
    If Target.Value >= 1 And Target.Value <= 12 Then
        ActiveSheet.Unprotect
        Range("O2").Select
    End If
End If
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
question:
Can this be modified for just a single worksheet (:OffsetFunction") rather than the workbook?
0
Frank FreeseAuthor Commented:
I selected your answer by modifying you Workbook_SheetChange sub as follows:

If Target.Address = "$M$3" Then
    If Target.Value >= 1 And Target.Value <= 12 Then
        Range("O2").Select
    Else
    MsgBox "A number between 1 and 12 is required", vbInformation, "Incorrect Value"
    Worksheets("OffsetFunction").Range("O2,M3").ClearContents
    Range("M3").Select
    Exit Sub
   
    End If

Thanks for pointing me in the right direction!
0
Harry LeeCommented:
fh_freese,

Yes, I can change it to protect only 1 sheet. The only catch is the sheet name has to stay the same for all the related workbooks.

If you want it to protect only 1 worksheet,

you have to modify it this way.

Private Sub Workbook_Open()
Dim WS As Worksheet
For Each WS In Worksheets
    If WS.Name = "OffsetFunction" Then
        Set WS = Sheets("OffsetFunction")
        With WS.Range("M3")
            .Locked = False
            .FormulaHidden = False
        End With
        WS.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
Next
End Sub

Open in new window


Also, watch out! I didn't know the sheet name with I wrote these codes but now I know.
You have to change the other code as well. This way, it won't react to other sheets.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim WS As Worksheet
For Each WS In Worksheets
    If WS.Name Like "OffsetFunction" Then
        Set WS = Sheets("OffsetFunction")
        On Error GoTo EndNow
        If ActiveSheet.Name = WS.Name Then
            If Target.Address = "$M$3" Then
                If Target.Value >= 1 And Target.Value <= 12 Then
                    ActiveSheet.Unprotect
                    Range("O2").Select
                End If
            End If
        End If
    End If
Next
EndNow:
End Sub

Open in new window

0
Frank FreeseAuthor Commented:
Thanks Harry,
I'll pick up your recommendation.
Appreciate your help
Frank
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.