[Webinar] Streamline your web hosting managementRegister Today

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

Prevent changing worksheets if data is not correct

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
Shaft960
Asked:
Shaft960
  • 9
  • 9
1 Solution
 
gbzhhuCommented:
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
 
gbzhhuCommented:
You can display a messagebox before the application.undo
0
 
Shaft960Author Commented:
Where does the

Private FalseEvent As Boolean  go

I copied and pasted and I got an error
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Shaft960Author Commented:
I figured out where the falseevent as boolean goes... but the coding provided is giving me run time errors
0
 
gbzhhuCommented:
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
 
Shaft960Author Commented:
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
 
Shaft960Author Commented:
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
 
gbzhhuCommented:
Put this before the msgbox

Sheets("130_setup").Activate
0
 
Shaft960Author Commented:
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
 
gbzhhuCommented:
Before Range("b5").Select

add

Sheets("130_setup").Select
0
 
Shaft960Author Commented:
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
 
gbzhhuCommented:
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
 
Shaft960Author Commented:
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
 
gbzhhuCommented:
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
 
Shaft960Author Commented:
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
 
gbzhhuCommented:
Code here looks fine.  where is the
Range("b5").Select
that was causing the error?
0
 
Shaft960Author Commented:
The Range("B5").select is in EVERY sheet in the on activate except the 130_setup sheet.
0
 
gbzhhuCommented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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