• 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.

Creating a modeless UserForm to guide users as needed

Jesse

New Member
Hi there, I'm creating an assessment tool (to evaluate water security in rural, resource-limited communities) and would like to provide guidance As Needed by the users. I'd also like to save screen space and not overwhelm the user, so I'd like to rule out using simple text boxes.


My inspiration is found in this assessment tool:

http://whqlibdoc.who.int/publications/2010/9789240500938_eng.xls


To see this GuidanceForm in action, upon opening the file, click "Enter assessment", and then from the ListBox control at the top choose "Table 1". A pop-up should appear entitled "Guidance". This can be turned on/off by a CheckBox (cell N17).


Note two things:

[list type=decimal]
[*]It is "modeless" (users can still interact with the spreadsheet while it is up - easy to do with
Code:
vbModeless

Its contents change depending on the position of the [code]ActiveCell
- I think this can be taken care of by using the Worksheet_SelectionChange[/code] event procedure.

[/list type=decimal]

I've had a look at the source code, it looks a bit tangled for a novice like me. Anybody have reasonable suggestions or a link where I could learn more? No big pressure, I'm wrapping up my thesis and this is lower priority.


Thanks in advance.


FYI: Background info for those interested: http://www.who.int/water_sanitation_health/publications/wsp_qa_tool/en/index.html
 
Hi Jesse ,


I tried downloading the Excel file thrice ; all three times , the download did not continue till the end of the file.


Is it possible that you can upload that file to SpeedyShare ( http://speedy.sh/ ) ?


Narayan
 
Great, thanks Narayank991 (and SirJB7!). I looked at some of their source code and found that the password to unprotect sheets is "SunShine". Anyways, I had a thought that may simplify this, if it can be done.


Idea #1: Have a static text in a TextBox in a column to the left, and have a button to hide/unhide the column as needed. The only problem is that there is an Excel glitch that messes up object positions when they are hidden with a column, the workbook is saved and closed, reopened, and columns are unhidden. (I had about 20 CheckBoxes [form control] pile on top of each other in each of 20 rows!)


Idea #2: Have changing text in a TextBox or drawn shape, and a button to make the shape visible/invisible, AND protect it so that it can be moved but not edited. I think I've seen ways to assign formulas/cell values to shapes? Perhaps I could write some
Code:
Worksheet_ChangeEvent procedure to get the position of the ActiveCell
which could return a value through a UDF to the cell address linked to the TextBox.


Haven't tried it yet, working on finishing the essentials for now!
 
Back
Top