• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to Inhibit the Worksheet_SelectionChange Procedure Procedure|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / How to Inhibit the Worksheet_SelectionChange Procedure Procedure|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosHow to Inhibit the Worksheet_Select…
sp_PrintTopic sp_TopicIcon
How to Inhibit the Worksheet_SelectionChange Procedure Procedure
Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 202
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
1
January 29, 2019 - 2:21 am
sp_Permalink sp_Print

Hello everyone My name is Maurizio and my problem is this:
Maybe I think to ask a question a little silly I have "stupid" that it is
But since I do not know the answer, I'll try with you.
My problem is this: Using a Userform I created a sort of Password Validation to protect a worksheet; And so far everything is OK
The problem, however, would be this: Since to call the userform I entered the code of opening within the procedure
(Worksheet_SelectionChange)
Silly wonder!
once the Password is found, the userform is rightly closed; For now at this point I would need a code that can tell the procedure (Worksheet_SelectionChange) no longer make me appear the userform until the application is restarted.
Do you think this opportunity exists?
Maybe using for example a code like this (Worksheet_SelectionChange = False)

(p.S) What would you do in my place Thanks
Greetings from A.Maurizio

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
January 29, 2019 - 9:17 am
sp_Permalink sp_Print

Hi Maurizio

I would have added a button on the sheet instead of asking user to select the cell.

Anyway you can try this code. It checks which cell was clicked and will display the form if cells E3 or E4 is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("E3:E4")) Is Nothing Then
UserForm1.Show
End If
End Sub

Hope this helps.

Sunny

Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 202
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
3
January 29, 2019 - 8:34 pm
sp_Permalink sp_Print

Hello SunnyKow How are you!
Listen Maybe the google translator did not express my words well; But I will try to explain myself better.
As you can see, my project works well in all its parts!
And it's a matter of protecting the Excel sheet; Here is the reason why I did not enter any key on the sheet itself, to open the userform.
That said, my question is as follows:
1 °) We hypothesize that you want to see my program with all the cells full of sensitive data; And let's assume that you want to please make some changes that I do not want to happen
There are two things: 1) I find the Password to unlock everything - 2) I have the userform will continue to ask you to enter a code three times and then automatically close the application
What I have not yet put into operation for "Praticita" of use being a test.
Ahora But if you find the way to enter by entering one of the four codes that I have made available; I would like that from then on, any changes I wanted to make to my worksheet can do so without appearing again my Userform Until the Application Restarted It's all here!
I hope I have been much clearer this time: Thanks for all the help you want to give me about it
Sincere greetings from A.Maurizio

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
January 30, 2019 - 9:02 am
sp_Permalink sp_Print sp_EditHistory

Hi Maurizio

I did not say I wanted to see your data. I just wanted to know what you want to achieve.

I was asking why you use a worksheet event instead of using a button (which in my opinion is easier to implement) to run the macro.

I am only guessing you want to allow only 3 tries to enter the password and if that fail then "lock" the application to prevent further tries.

The user will then need to close and reopen the file (restart?) to retry again. Is that what you wanted?

If that is the case I suggest you store a counter in one cell and check if the number of tries is less than 3 before displaying the userform.

Upon "restart" your macro (workbook open event) will need to reset the counter to 0.

Sunny

Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 202
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
5
January 31, 2019 - 5:04 am
sp_Permalink sp_Print

Hello SunnyKow listen to what I needed was simply this Code:
"Application.EnableEvents = False"
That works great; And I found it on a vba book I have in my bookshop.
That said, however, immediately discarded as it is never very clever to deactivate the functions of excel; Even itself and only a trial project.
So I finally adopted the simple Function (End) to close the userform in case the exact password is found here!
and the Code (Application.Quit) In case you exceed three attempts to enter.
And so far all Ok!
But now I do not know why but there is another problem here I can not give an answer, because I tried them all.
And the question is this:
Every time I enter an incorrect password and then I press the (OK) button the Textbox instead of zeroing and starting again from Zero; Continue from the Numbers I have previous letters:
Can you give me a look?
The codes to enter are written in the Help of the program itself. Thanks
For the rest everything works wonderfully Hello and Thanks

This is the link to download the whole again :

https://app.box.com/s/zl70o59u.....e3bw501kzs

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
6
January 31, 2019 - 11:17 am
sp_Permalink sp_Print sp_EditHistory

Hi Maurizio

Yes, I know you will need to set Application.EnableEvents=False but I was trying to convince you not to use the Worksheet Events as they can get very messy if you are not careful. You will need to switch it off and on again in your codes.

Although you reset the Campo textbox to blank, you did not reset the Password variable and your code keeps on adding Password to the  Campo text box.

In your Private Sub Cmd_Tasto_OK_Click() module you should add the line in red to reset the Password.

Tentativo = Tentativo + 1 'Aumenta di 1 il numero dei Tentativi
Campo.Text = ""
'Added by Sunny
Password = ""

Sunny

Avatar
Dave Morrison
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 21, 2018
sp_UserOfflineSmall Offline
7
January 31, 2019 - 2:36 pm
sp_Permalink sp_Print sp_EditHistory

A.Maurizio said

That said, however, immediately discarded as it is never very clever to deactivate the functions of excel; Even itself and only a trial project.

 

  

Nothing wrong with application.enableevents=false as long as you have application.enableevents=true at the end of the code.

 

As previously stated, somewhere before focus on the textbox again, use password="" or campo.clear

Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 202
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
8
February 1, 2019 - 1:10 am
sp_Permalink sp_Print

Hi Dave Morrison Thanks for the advice I will take care of all this
But I managed to put all the pieces together and now my project Apportanto Substantial changes to make it work just as I wanted from the beginning
If you want, try it and let me know.
Clearly without many claims?
You all are undoubtedly better than me; Hello and thanks.

These are the links to Download the Modified Project and the complete List in the Text Document.

1) https://app.box.com/s/hh0lu07i.....5tqo65lh2c

2) https://app.box.com/s/csnj409p.....bgf0fk01lp

I offer this greetings to all of you and a warm thank you A.Maurizio

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Roslyn Finlayson, Shanna Henseler, LAN H
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6210
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31888
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.