?
Solved

Prevent changing worksheets if data is not correct

Posted on 2005-03-18
18
Medium Priority
?
148 Views
Last Modified: 2010-05-02
How can I prevent someone from changing worksheets if a value is incorrect in the activeworksheet...

like something simple like if A1 <> 2 just for example

If you try to change sheets and A1 is not equal to 2 then you get an error message saying that you need to fix the problem before changing worksheets...

I tried the deactivate but the and the error message come up but it allow you to exit.. I tried cancel and ...

Thank you..
0
Comment
Question by:Shaft960
[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
  • 9
  • 9
18 Comments
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13573377
Private FalseEvent As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not FalseEvent Then
        If Range("A2") <> 2 Then
            FalseEvent = True
            Application.Undo
        End If
    Else
        FalseEvent = False
    End If
End Sub
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13573382
You can display a messagebox before the application.undo
0
 

Author Comment

by:Shaft960
ID: 13579961
Where does the

Private FalseEvent As Boolean  go

I copied and pasted and I got an error
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Shaft960
ID: 13580990
I figured out where the falseevent as boolean goes... but the coding provided is giving me run time errors
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13589813
What is the error and at what line.  Put a breakpoint and step through.

ps.  I assumed that you are in EXcel doing VBA (not automating Excel via VB) correct?
0
 

Author Comment

by:Shaft960
ID: 13589839
For now I just put this weak code in the deactivate of the 130_setup sheet

Private Sub Worksheet_Deactivate()
If Range("a53") <> "Master" Then
If Range("af154") > 0 Then

  '  I WANT IT TO RETURN TO THE sheet "130_Setup" right here and then give a message

MsgBox "Return to the Setup and select the appropriate 'USE POSITION' button", vbCritical


End If
End If
End Sub
0
 

Author Comment

by:Shaft960
ID: 13589847
All I want it to do is on the leaving of a sheet named 130_setup.. If af154 > 0 then stay on that sheet and give a message to press a button

Now.. they read the message and have to go to the sheet on there own
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13589886
Put this before the msgbox

Sheets("130_setup").Activate
0
 

Author Comment

by:Shaft960
ID: 13589985
I get a run time error 1004

It highlights a code in the activate of the sheet that I go to ... the code is

Range("b5").Select
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13590223
Before Range("b5").Select

add

Sheets("130_setup").Select
0
 

Author Comment

by:Shaft960
ID: 13590238
Wouldn't that me that every time that I went to that sheet it would go to the 130 file regardless if the data in the 130 is correct?
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13590538
I am a bit confused because you don't name all the sheets, so please give each sheet a name so that i know which sheet we are moving to and from

The way I see so far is that you are in sheet 130_setup when you move to another sheet you want to check that some cells contain certain values, if they are not you want to remain in sheet 130_setup and display a message to the user, is that correct?  

The code above I suggested

Sheets("130_setup").Select

I was assuming that you are putting it in the activate event of sheet 130_setup is this so?

I am off to lunch and be back in an hour and half or so

Cheers
0
 

Author Comment

by:Shaft960
ID: 13597088
I have about 40 different sheet names.  In each of these sheets there is numerous lines of coding in the on worksheet_activate.. one line is to set the focus to cell b5.  Range("b5").Select

The program deals with monthly totals.  When you first setup a month you are in a sheet named "130_setup".

You need to enter values into the "130_setup" sheet to transfer to all of the other sheets.  

What I want it to do is if "130_setup" sheet does not have certain data .. I want it to remain there.  If the user clicks one of the 40 sheets it will bring up a message which tells them that they need to enter that data.





0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13599670
Ok I understand you but I can't visualise what your code is like.  can you post your code in the deactivate event of the 130_setup sheet and I will see if I can find why Range("b5").Select is failing
0
 

Author Comment

by:Shaft960
ID: 13600099
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   
           
         If Sh.Name = "130_setup" Then Exit Sub
   
    Application.ScreenUpdating = False
         
        If Application.Sheets("130_setup").Range("p9").Value <> "" Then
            If Application.Sheets("130_setup").Range("i64").Value = "" Then
               ' Application.Sheets("130_setup").Activate
                MsgBox "You need to co"
            End If
            End If
       
        Application.ScreenUpdating = True

   
   

End Sub
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13601932
Code here looks fine.  where is the
Range("b5").Select
that was causing the error?
0
 

Author Comment

by:Shaft960
ID: 13604319
The Range("B5").select is in EVERY sheet in the on activate except the 130_setup sheet.
0
 
LVL 12

Accepted Solution

by:
gbzhhu earned 500 total points
ID: 13620560
Ok, I now understand everything and reproduced the problem.  Basically you are trying to select a cell but that cell is not the active sheet it is on another sheet.  This is what I suggest

- add a module to your project
- add the following code to the module

Public Sub CheckSheet_130_Setup()
    If Sheets("130_Setup").Range("b5") = "" Then
        MsgBox "say something"
        Sheets("130_Setup").Activate
    End If
End Sub

- change the if condition to whatever you want to test for
- in every sheet except 130_Setup, in the activate event add the following line and make sure it is the last line in that event

CheckSheet_130_Setup

Let me know how this goes
 


0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month13 days, 4 hours left to enroll

777 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