Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
ShiftKey Enable/Disable with password InputBox
6 views
Skip to first unread message
nood...@gmail.com
Dec 10, 2019, 11:18:01 PM12/10/19
to
Hi All
The file I am working on will be rolled out for use by both our operations people, and every customer that uses our local services; it makes them responsible for the accuracy of detail when creating a data file for upload to our servers with their requirements.
I was stunned, when extracting data from our mainDB to find zero continuity with respect to standard/Referential integrity of certain data, mainly:
Address Line .1
Address Line .2
That said, this file forces all users to use the comboboxes and validations enforce standards across all fields of input.
I disabled much of the workbooks features, including the UI_Ribbon via Workbook_Open() which limits the user to accessing only the Main_Menu_Form I created, which in-turn allows them to navigate to the areas I want them going to.
An avid user can Google the ShiftKey Hack, which is not what I want them to be able to do, for obvious reasons. Many posts regarding this generally point to the following link for disabling it:
http://pixcels.nl/disable-shift-key-on-open/
IMO, it lacks the ability, as the creator, for me to enable the key by way of a Password Input.
I toyed with the following, hence the reason I am here is it doesn't work.
Option_Explicit
Private mbMacrosEnabled As Boolean
Dim myPassword As String
___________________________________________________
Public Function CheckShiftOnOpen()
Dim myPass As String
Dim byPass As Integer
myPassword = "The Password"
If Not mbMacrosEnabled Then
MsgBox "Left-Shift By-pass Functionality is Disabled! Enter Password to Enable.", vbCritical, "Security Warning"
On Error Resume Next
Set myPass = Application.InputBox(Prompt:="Enter Security Code:", Type:=8)
If Not myPass = myPassword Then
byPass = MsgBox("Code Incorrect! Try Again.", vbOKCancel)
If byPass = vbCancel Then Exit Function
With Application
.EnableCancelKey = True '***
End With
End If
End If
End Function
Sub customUIonLoad(ribbon As IRibbonUI)
ThisWorkbook.CheckShiftOnOpen
With Application
.EnableCancelKey = xlDisabled
End With
End Sub
Now, I know that *** ".EnableCancelKey = True" syntax is wrong, I am merely using it as an analogy as to what I am wanting to achieve.
A ShiftKey Password is achievable in Access:
https://stackoverflow.com/questions/32991071/bypass-shift-with-pop-up-password-box
Can anyone suggest a workaround for this please:
As always
TIA
Mark.
The file I am working on will be rolled out for use by both our operations people, and every customer that uses our local services; it makes them responsible for the accuracy of detail when creating a data file for upload to our servers with their requirements.
I was stunned, when extracting data from our mainDB to find zero continuity with respect to standard/Referential integrity of certain data, mainly:
Address Line .1
Address Line .2
That said, this file forces all users to use the comboboxes and validations enforce standards across all fields of input.
I disabled much of the workbooks features, including the UI_Ribbon via Workbook_Open() which limits the user to accessing only the Main_Menu_Form I created, which in-turn allows them to navigate to the areas I want them going to.
An avid user can Google the ShiftKey Hack, which is not what I want them to be able to do, for obvious reasons. Many posts regarding this generally point to the following link for disabling it:
http://pixcels.nl/disable-shift-key-on-open/
IMO, it lacks the ability, as the creator, for me to enable the key by way of a Password Input.
I toyed with the following, hence the reason I am here is it doesn't work.
Option_Explicit
Private mbMacrosEnabled As Boolean
Dim myPassword As String
___________________________________________________
Public Function CheckShiftOnOpen()
Dim myPass As String
Dim byPass As Integer
myPassword = "The Password"
If Not mbMacrosEnabled Then
MsgBox "Left-Shift By-pass Functionality is Disabled! Enter Password to Enable.", vbCritical, "Security Warning"
On Error Resume Next
Set myPass = Application.InputBox(Prompt:="Enter Security Code:", Type:=8)
If Not myPass = myPassword Then
byPass = MsgBox("Code Incorrect! Try Again.", vbOKCancel)
If byPass = vbCancel Then Exit Function
With Application
.EnableCancelKey = True '***
End With
End If
End If
End Function
Sub customUIonLoad(ribbon As IRibbonUI)
ThisWorkbook.CheckShiftOnOpen
With Application
.EnableCancelKey = xlDisabled
End With
End Sub
Now, I know that *** ".EnableCancelKey = True" syntax is wrong, I am merely using it as an analogy as to what I am wanting to achieve.
A ShiftKey Password is achievable in Access:
https://stackoverflow.com/questions/32991071/bypass-shift-with-pop-up-password-box
Can anyone suggest a workaround for this please:
As always
TIA
Mark.
GS
Dec 11, 2019, 5:26:17 AM12/11/19
to
Mark,
I get the impression that you are trying to customize the Excel UI to suit your
project. Please explain the why/how of your disabling of workbook features
including the UI_Ribbon. There may be a better way to do this sans macros!
--
Garry
Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
I get the impression that you are trying to customize the Excel UI to suit your
project. Please explain the why/how of your disabling of workbook features
including the UI_Ribbon. There may be a better way to do this sans macros!
--
Garry
Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
nood...@gmail.com
Dec 11, 2019, 5:16:38 PM12/11/19
to
Hi Garry
Absolutely correct in your assumption of the UI.
I work for a Transport Co, over the years, there seems to have been a degradation in thinking and the approach to Data Integrity.
Main culprit being attrition of experienced personnel, the people stepping up to continue on have not been trained properly, resulting in mismatched data in the wrong fields.
Disabling everything that could distract or tempt users to deviate from what needs to be, is achieved with the following:
'Sub File_Open()
'With Application
' .ScreenUpdating = False
' .EnableEvents = False
' .WindowState = xlMaximized
'.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
'.DisplayScrollBars = False
'.DisplayStatusBar = False
'.DisplayFormulaBar = False
'.DisplayFullScreen = True
'.EnableEvents = True
'.ScreenUpdating = True
'End With
'With ActiveWindow
'.WindowState = xlMaximized
'.DisplayWorkbookTabs = False
'.DisplayHeadings = False
'End With
'Sheets("Splash").Select
'With Application
' .Goto Reference:="R4C4"
'End With
'End Sub
Essentially, this renders the whole workbook as just a screen with a Title-bar and the Main-Menu Form which is populated with the cmdBtn's for navigating only.
When the workbook is closed it runs the Open code in reverse returning Excel to it's native state.
Cheers
Mark.
Absolutely correct in your assumption of the UI.
I work for a Transport Co, over the years, there seems to have been a degradation in thinking and the approach to Data Integrity.
Main culprit being attrition of experienced personnel, the people stepping up to continue on have not been trained properly, resulting in mismatched data in the wrong fields.
Disabling everything that could distract or tempt users to deviate from what needs to be, is achieved with the following:
'Sub File_Open()
'With Application
' .ScreenUpdating = False
' .EnableEvents = False
' .WindowState = xlMaximized
'.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
'.DisplayScrollBars = False
'.DisplayStatusBar = False
'.DisplayFormulaBar = False
'.DisplayFullScreen = True
'.EnableEvents = True
'.ScreenUpdating = True
'End With
'With ActiveWindow
'.WindowState = xlMaximized
'.DisplayWorkbookTabs = False
'.DisplayHeadings = False
'End With
'Sheets("Splash").Select
'With Application
' .Goto Reference:="R4C4"
'End With
'End Sub
Essentially, this renders the whole workbook as just a screen with a Title-bar and the Main-Menu Form which is populated with the cmdBtn's for navigating only.
When the workbook is closed it runs the Open code in reverse returning Excel to it's native state.
Cheers
Mark.
GS
Dec 11, 2019, 5:56:54 PM12/11/19
to
> '.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
What does this do, exactly?
What cmdBtn's are you using?
Would having only your own custom Ribbon tab display only when your workbook is
active be an advantage?
What does this do, exactly?
What cmdBtn's are you using?
Would having only your own custom Ribbon tab display only when your workbook is
active be an advantage?
nood...@gmail.com
Dec 11, 2019, 8:58:18 PM12/11/19
to
Hi Garry
What does this do, exactly?
That handy line removes the UI Ribbon totally.
Essentially the On_Open Code removes everything and the user is left with a blank canvas and the Menu_Form I created for navigation.
What cmdBtn's are you using?
CmdBtn's:
Menu broken into 3 sections:-
Activities:
- Add New Cust
- Update Cust Status
- Generate New Job(s)
- Upload new Job(s)
Search: - (results via ListBox, just list & no interaction)
- Job(s)~[ By Date ]
- Job(s)~[ By Name ]
- Job(s)~[ All ]
View: - (results via ListBox, just list & no interaction)
- Check Job(s) Upload Status
Would having only your own custom Ribbon tab display only when your workbook is active be an advantage?
As for a custom Ribbon, I don't see it as a requirement given I only want them to be able to access this Menu and what it navigates to.
I Hope this provides some clarity into my end goal, and why I am removing any chance for users to circumvent it's use, intended or accidental.
I would have much preferred to do this in an Access Environment, but! according to our I.T. Dept, It comes with a lot of Security and Path Environment issues that could make it harder than it needed to be for differing clients, so this Excel option is simpler, apparently!
Cheers
Mark.
What does this do, exactly?
Essentially the On_Open Code removes everything and the user is left with a blank canvas and the Menu_Form I created for navigation.
What cmdBtn's are you using?
Menu broken into 3 sections:-
Activities:
- Add New Cust
- Update Cust Status
- Generate New Job(s)
- Upload new Job(s)
Search: - (results via ListBox, just list & no interaction)
- Job(s)~[ By Date ]
- Job(s)~[ By Name ]
- Job(s)~[ All ]
View: - (results via ListBox, just list & no interaction)
- Check Job(s) Upload Status
Would having only your own custom Ribbon tab display only when your workbook is active be an advantage?
I Hope this provides some clarity into my end goal, and why I am removing any chance for users to circumvent it's use, intended or accidental.
I would have much preferred to do this in an Access Environment, but! according to our I.T. Dept, It comes with a lot of Security and Path Environment issues that could make it harder than it needed to be for differing clients, so this Excel option is simpler, apparently!
Cheers
Mark.
GS
Dec 11, 2019, 9:16:15 PM12/11/19
to
Ok, so then your Menu_Form serves as a dashboard-type UI for users, where
cmdBtns are worksheet controls OR userform controls? I suspect the latter in
that its window takes over the screen, perhaps?
Sounds like much more work than need be to me!
cmdBtns are worksheet controls OR userform controls? I suspect the latter in
that its window takes over the screen, perhaps?
Sounds like much more work than need be to me!
nood...@gmail.com
Dec 11, 2019, 10:57:36 PM12/11/19
to
Garry
It may well be more than it needs to be, but!
I prefer to err on the side of caution, along with the fact it makes my boss warm n fuzzy knowing the file is somewhat idiot-proof.
There is also the fact this has taken me along paths I had not gone down before, which is always nice to learn new things.
Much appreciation for your time, thoughts & knowledge.
Cheers
Mark.
It may well be more than it needs to be, but!
I prefer to err on the side of caution, along with the fact it makes my boss warm n fuzzy knowing the file is somewhat idiot-proof.
There is also the fact this has taken me along paths I had not gone down before, which is always nice to learn new things.
Much appreciation for your time, thoughts & knowledge.
Cheers
Mark.
GS
Dec 12, 2019, 12:23:05 PM12/12/19
to
Have fun!
FWIW
My suggested approach will run as a stand-alone Excel-based application where
your users will only be able to do what you allow them to do, and since it runs
its own instance of Excel (not the user's default instance), its UI can be
whatever you want it to be. (Food for thought!)
0 new messages