• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

User form via QAT

bobhc

bobhc

Excel Ninja
Good day all

I have a spread sheet to take user input, there are many users. To make the data input easier and to alleviate the need for the user to scroll through the spread sheet to find the next empty row I added the form icon to the QAT.

The form lists the field names with a box next to the fields for data input, this is much better for user input, it is friendlier to the eye for those who do not use spread sheets…….but the form does not open automatically on top of the spread sheet when it is opened, you have to click the form icon on the QAT.

Is there away I can make this happen and stop the spread sheet form taking direct user input, all input must be through the form.
 
In the VBE, go to the ThisWorkbook module and put something like this:

[pre]
Code: 
Private Sub Workbook_Open()
UserForm1.Show
End Sub
As long as you don't put False after the Show command you should be okay.

To let user interact with workbook while form is open:

Private Sub Workbook_Open()
UserForm1.Show False
End Sub
[/pre]
 
Luke M

I have tried your VBA and I get an error....but before you read on please understand at this time I know more about Quantum physics than I do VBA, below is the error message and a copy of my VBA


Compile error:

Ambiguous name detected: Workbook_open


Private Sub Workbook_Open(Personal_details)

UserForm1.Show

End Sub

Private Sub Workbook_Open(Personal_details)

UserForm1.Show False

End Sub
 
Error is because you have two macros with the same name.


I think I ended up causing extra confusion in my earlier post, I meant for you to just use one of the other. The first one (without the false) is the one I think you want since you said that you don't want people to be able to access the workbook while the form is open.


EDIT: Re-reading your form, I think I further misunderstood you. :(

I thought you were using a UserForm, but I think you are just using the regular Data form within XL. In which case, macro should be something like:

[pre]
Code: 
Private Sub Workbook_Open()
'Where is the table of data
Worksheets("My Data").Range("a1").Select
'Show the form
ActiveSheet.ShowDataForm
End Sub
[/pre]
Again, sorry for the confusion.
 
@SirJB7

Hi, myself!

I'd swear I haven't drunk any alcohol today... I'd swear it even for William Gates III's soul, if necessary...

So...

Why am I seeing double? Maybe too much hours at computers...

:)

Regards!
 
Luke M

You have no need to apologize.As I said I am the one who does not understand, I am taking your VBA and totally messing it up. I will in time look at VBA


Run-time error ‘9’:

Subscript out of range


Private Sub Workbook_Open()

'Where is the table of data

Worksheets("Personal_details").Range("details").Select

'Show the form

ActiveSheet.ShowDataForm

End Sub


As you can see I do not know VBA
 
Is the Personal_details sheet visible in the workbook?

You have a range named "details"?

You may need to first select the sheet, and then the range then...

[pre]
Code: 
Private Sub Workbook_Open()
'Where is the table of data
Worksheets("Personal_details").Select
ActiveSheet.Range("details").Select
'Show the form
ActiveSheet.ShowDataForm
End Sub
[/pre]
 
High Luke M

Still getting

Run-time error ‘9’:

Subscript out of range


I did select the range "details" before pasting in your VBA in but I am doing it all wrong.

I will have to read up on VBA (that's is going to take some time)

Sorry for wasting your time and many thanks for trying to help
 
Hi, b(ut)ob(ut)hc!

Does the range "details" refers to worksheet "Personal_details"?

Regards!
 
SirJB7

I have uploaded the start of the spread sheet,.....but would appreciate it if you could stifle your laughter


https://www.dropbox.com/sh/okby8mztvo9g6cj/wgbzUz31zF/Personal_details.xlsm
 
Hi, b(ut)ob(ut)hc!


I think I found it: the name of the sheet is "Personal details" instead of "Personal_details".


And no need to stifle anything, why sould I do such a thing? Look, yesterday I participated in this topic:

http://chandoo.org/forums/topic/visual-basic-issues#post-28479

There's reflected what I do think and believe about learning process, in which I'm involved too.


Regards, old-dog-new-tricked!
 
Good day SirJB7

I renamed the sheet to Personal_details and it know displays the input form when the work book is opened. I salute and thank you.......but, when I open the workbook the named range is high lighted, the whole range,is this normal. My other question is do you need a named range, I was wanting a sheet where users could just keep entering data via the form with out running out of range.
 
Good day

A bit of head scratching but I have sorted the problem of the sheet being high lighted by removing "ActiveSheet.Range("details").Select".

Is it possible to have it so that when a person has finished entering data and they close the form it also closes the workbook so that the sheet is not open for editing.
 
Good day

I have put this code in to the workbook but when I close the work book it closes all open work books, not what I am after.


Private Sub Workbook_Open()

'Where is the table of data

Worksheets("Personal_details").Select

'ActiveSheet.Range("details").Select

'Show the form

ActiveSheet.ShowDataForm


If CloseMode <> 1 Then Cancel = 1

Application.DisplayFullScreen = False

Application.Quit

End Sub


The VBA locks out users from editing the work sheet directly.......but it also locks out the author (me)form editing the work sheet directly, is there a way to stop it closing all open work books and also maybe a keyboard combination to open the work book for editing
 
Change your last line to simply be

[pre]
Code: 
ThisWorkbook.Close 'Closes just the 1 workbook
instead of

Application.Close 'Closes everything
[/pre]
Which will just close the workbook with code in it, rather than all of XL (the application).


To edit the workbook, you could hold down the Shift key while you open the workbook. Holding down Shift prevents any macros from being triggered during the opening process.
 
Good day


I would like to take the opportune to thank Luke M and sirJB7 for their help. I have learned a small amount about VBA and have achieved the desired end result. On closing the work book other open books are not affected, and I knew about the shift key from work on Access, unfortunately so do others I was hoping for a work around that would allow me to enter a key combination in VBA that would allow me to open the work book for editing but disable the shift key
 
You're very welcome bobhc. I would say that I too am thankful for those who have passed on their knowledge to me. My personal goal is that each "generation" of students becomes a little bit wiser than the one before. =)


Unfortunately, you can't disable the shift key function due to that being an internal security measure. If there was no way to disable macros, that could be exploited by hackers with ill-intent.


It's not full proof, but if you've got a unique user name for XL, you might be able to do something like:

[pre]
Code: 
Private Sub Workbook_Open()
If Application.UserName = "Luke M" Then
'It's ME! Don't do anything
Exit Sub
End If

'Where is the table of data
Worksheets("Personal_details").Select
'ActiveSheet.Range("details").Select
'Show the form
ActiveSheet.ShowDataForm

If CloseMode <> 1 Then Cancel = 1
Application.DisplayFullScreen = False
Application.Quit
End Sub
[/pre]
Note that User Name is whatever you've told XL your name is, NOT what your Windows login is. This is not full proof either, as someone could potentially just change their XL user name to match yours. But it's an idea.
 
Good day Luke M


One of the reasons I have time to spare and work on this at the moment is that I have a few days off work and I have scored brownie points with the wife by getting the bedroom decorate, one of the few times you get an ice cold beer with out asking....but back to the spread sheet. I will take this to work and try it out, one of two things will happen, it will work and I will salute you sir for you help, or it will fail and totally stuff the server upon which I will depart the building before IT discover whose machine was responsible.:)
 
Ha ha! =D

Thanks for the smile. I'll be hoping that you don't stuff the server then.
 
Hi, b(ut)ob(ut)hc!


I've been out for the weekend, just arriving back. Let's read above a little... I see Luke M aided you with part of your issues and I agree with you about shift key usage...


But... (remember that there's always a but...)


... there's a workaround. Not too tricky, not too easy, and as bulletproof as any password schema is within Excel (keep in mind password recovering, password cracking, Elcomsoft, and all that stuff).


Of course this involves getting hands a little dirty with VBA, but nothing from outerspace. The general idea is:

a) protect workbook structure so as to don't let users hide/unhide worksheets

b) protect worksheets calculated cells so as to don't let users screw your design

c) protect VBA project so as to don't let users sniff around

...(nothing strange up to this moment)

d) add a dummy/splash worksheet to "welcome" shifted users

e) add an auto open macro Sub procedure

f) add a before save macro Sub procedure

g) recommended but not necessary a save command button


Auto open procedure.

Here you'll place the code for:

- unprotect workbook

- unhide other worksheets (normal use)

- hide splash worksheet (only worksheet not hidden at save time)

- protect workbook

If the so-self-thought-clever-shifted user tries to cheat you using shift key trick, he'll be bypassing this auto open procedure too, and he'll be only looking at the splash sheet, and as he won't have the password for changing workbook structure he won't be able to neither unhide nor use your data (nor save nor nothing).

If he never attempts to open the workbook using shift trick, he won't ever know your protection measures scheme.


Before save procedure.

Here you'll place the code for:

- check against unproper open methods, if so, cancel save

- unprotect workbook

- unhide splash worksheet (you can't hide all worksheets)

- hide other worksheets

- protect workbook


The passwords could be all the same or as my recommendation:

- VBA project, just stored in your head

- worksheet cells, different from previous one

- workbook structure, same as above

The last two should be embedded (defined, stored) within your VBA code as auto open and before save procedures use them. It'll all depend on the passwords you choose, if they're algorithm based, or word or phrase based, ... but always remember that they can be sniffed with a file/hexadecimal editor.

So:

- define them as variables instead of constants

- set a piece of code that assign them values from a procedure

- don't use string constants ("AB"), use char functions (chr$(65)&chr$(66))

- always of course, strong passwords or passphrases of more than 12 or better 16 chars


With all these, if a user breaks into your workbook bypassing your efforts, he well deserves his achievement :)


I know you understood everything but don't know how to implement that... for the moment. Now it's 01:00 AM, tomorrow (today, bah) afternoon I'll send you a sample empty workbook as starting kick off, if you wish. Please let me know about this.


Regards!
 
Good day sirJB7

I have read and reread your post and as you say it is time to get the overalls on and get down and dirty with VBA, due to other work commitments this is going to take some time. I will read up on VBA when I have finished reading the book (dummies)on Formulas and Functions.....but I will let you know at some far distant time how I get on with my learning curve, as for your kind offer to upload a sample I am sure this will show the right way to go and help the enlighten VBA, thank you.
 
Hi, b(ut)ob(ut)hc!


Here's the link to the sample file:

https://dl.dropbox.com/u/60558749/User%20form%20via%20QAT%20-%20Secured%20workbook%20schema%20%28for%20bobhc%20at%20chandoo.org%29.xlsm


The file has 4 worksheets:

1) Splash: to be displayed if shift-opened... but bad luck, won't be able to do anything except closing, updating it or not, but without any change exception made of time stamp

2) 1st/2nd/3rd sheets: to be displayed if opened normally...


The workbook is protected with password: ABCDEFGHIJKLMNOP

The worksheets are protected with password: abcdefghijklmnop

The VBA project is protected with password: OldDogNewTricked


The first 2 are defined on a char by char basis within the module modSecureWorkbookSchema, you can change them as you want, but firstly do this:

- unprotect the workbook

- unhide all sheets (I hope that only Splash sheet, otherwise we're in trouble)

- unprotect all sheets

Then change the password definitions and click on the cyan/green buttons.

If any issue, go to the immediate window pane (Ctrl-G if not displayed from VBA editor) and type: giOpenStatus = 1547 (and press enter).

Then retry.


The passwords are not kept in memory in the variables used to store them, this is only done while the active procedure that needs them is running. This makes a little harder to stole them by just examining the file with an hex-editor, discovering the names sPassword1 and sPassword2 (that's why I left understandable names), and then trying to print them using the immediate window.


In the same way, the functions that generate the passwords are names simply sGeneratePassword1 and sGeneratePassword2, as they should receive a parameter (I called it plToken) with the proper value... that is calculated for each one as:

a) gklOne * gki13 + gklTwo * gki7 + gki17

b) gklOne * gki7 + gklTwo * gki13 + gki17

Just to make things a little harder to sniffer guys. If they go to the immediate window and type ?sGeneratePassword1 or 2, first an error will be raised as of the missing parameter, and then nothing will be displayed, unless they find the exact number (different for each one) that is calculated with the above formulas. You can change those values too, as you want, and you only have to do it at the Const definition section at the beginning of the module.


How does this work?

a) workbook is always stored (saved) with all sheets protected, Splash sheet visible and all other sheets hidden

b) if opened normally (without shift key pressed) the visibility of the sheets get inverted and a global variable (giOpenStatus) is set to 1547: actually the result of the multiplication of three constants, that you can change at your will... so user can work normally

c) if opened trickily (with shift key pressed) no sheet visibility is changed and no global variable is set as no open macro is run... so user can't do anything but closing the workbook

d) saving and closing from the menu or ribbon are disabled via Msgbox and Cancel returned values, to ensure that Saving/Closing are only performed by code (cyan and green buttons for saving, and green and red button for closing)


I suggest you to make a copy of the worksheet, then remove the VBA protection so as to facilitate debugging if needed or wanted while analyzing it. Unlocked cells in sheets are yellow shaded; anything else is locked. Filtering and sorting are available even protection, just in case you want to leave auto filter on.


Dive into the code, it's not so long, it's easy to follow, and annotate all your doubts.


Well, not much more... now you've got a new toy to play around for a while. In the meantime don't hesitate to ask everything that you don't catch up.


Regards, old dog new tricked!
 
You must log in or register to reply here.
Back
Top