This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!
Solved
Move from one cell to another
Posted on 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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
5 Comments
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
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
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
Active 1 day ago
question:
Can this be modified for just a single worksheet (:OffsetFunction") rather than the workbook?
Can this be modified for just a single worksheet (:OffsetFunction") rather than the workbook?
Active 1 day ago
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("M3").Select
Exit Sub
End If
Thanks for pointing me in the right direction!
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("M3").Select
Exit Sub
End If
Thanks for pointing me in the right direction!
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.
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.
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
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…
Suggested Courses
Course of the Month8 days, 17 hours left to enroll
721 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.