I have a user form with various text boxes, option buttons, tick boxes and combo boxes.
The form is to be used to raise and update various jobs/faults.
I wish to have one of the tick boxes as an option to close the job down. Can anyone help me with the code that will lock out the form for editing when it is clicked.
Also I dont know where to start with code that will grey out certain areas of the form once a certain value is selected from a combo box.
Cheers
Last edited by laffers; 04-05-2011 at 06:21 PM.
Hello laffers,
I believe you will receive more responses and help if you post a sample workbook. People are not inclined to try an recreate what you have already done and spend time posting questions and waiting for answers to find out if the workbook matches what you have done.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Apologies, sample workbook attached.
Hello laffers,
I have added a sample UserForm based on your requirements to the attached workbook. The code for the UserForm is shown below. There is a button on "Sheet1" to display the UserForm.
Please Login or Register to view this content.
Brilliant. Cheers Leith!
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
I did have this marked as solved but require some modification to it.
The userform I have has a search facility. Its used to look up certain jobs and tasks. I am going to use the checkbox to 'close' the job down. Basically when the job or task is finished then the user checks close and the form is greyed out disabling the ability to edit the form anymore.
The problem is when using the example above, when I recall the job again the textboxes are no longer greyed out and can be edited. Is there anyway I can permanently grey them out?
Also by checking the closed box, can I affect the value in another textbox eg, changing from Open to Closed.
Hello laffers,
That was tricky to do. The difficult part was getting the Enabled properties of the controls to be saved as False. Once the user form is closed, the values of the controls are lost. A public boolean variable is used to retain the state of the check box before the user form is closed. The macro is then called. If the value of the variable is True then all the controls Enabled properties are set to False and the workbook is saved. When the user form is displayed again, all the controls are disabled. I have attached an example workbook.
Lockout_Controls Module Code
Please Login or Register to view this content.
UserForm CheckBox Code
Please Login or Register to view this content.
Hi Leith,
I was having problems with that code. Having locked the form, I tried to close it using the 'x' and I received a Run Time Error 1004: Programmatic Access to Visual Basic Project is not trusted
The code also stopped here:
Tools>Macros>Security then the Trusted Sources tab and click Trust access to Visual Basic Project sorts that.Please Login or Register to view this content.
Its looking good so far. I'll have to test on my project tomorrow in work and I'll let you know.
Cheers
Actually, having had a play around a bit more, If I enter a value in the textbox and check the box then it greys out which is good.
When I close the form and then recall it, the textbox is empty?
Hello laffers,
Whenever a UserForm is closed, all the controls values are lost. I meantioned that in my previous post.
Once the user form is closed, the values of the controls are lost
You didn't mention retaining the values of controls after they were disabled. If that is what you want to do then I need to make some changes to the macro.
Apologies for the confusion Leith.
The userform is used to recall rows of data from the spreadsheet.
Enter the job number in the search box, hit 'Search' and the textboxes are populated with the data. What I would like to happen next is that when you check Closed the job is closed and no more alterations can be made. If however you wish to review the job in the future you can by Searching by job number as usual but the textboxes containing the data are locked out. I hope this explains. I know it sounds complicated. I can knock up a sample if you want?
Hello laffers,
A sample workbook would be very helpful.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks