Solved

Move from one cell to another

Posted on 2014-02-28
5
346 Views
Last Modified: 2014-02-28
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
Comment
Question by:Frank Freese
  • 3
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39895777
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
 

Author Comment

by:Frank Freese
ID: 39895874
question:
Can this be modified for just a single worksheet (:OffsetFunction") rather than the workbook?
0
 

Author Closing Comment

by:Frank Freese
ID: 39895963
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
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39896170
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
 

Author Comment

by:Frank Freese
ID: 39896212
Thanks Harry,
I'll pick up your recommendation.
Appreciate your help
Frank
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question