Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

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.
0
Frank Freese
Asked:
Frank Freese
  • 3
  • 2
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now