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

Userform not displaying properly

T

txfrazier

New Member
Hello,


I have a problem that is difficult to explain without having the actual form in front of you but here goes . . .


I have a user form with checkboxes (I'll call them parent checkboxes)that activates and or displays other textboxes (child textboxes) and/or checkboxes (child checkboxes) when they are checked. The problem I'm having is that when the user re-opens the form, the childe textboxes or childe checkboxes that were activated because the parent checkboxes were checked are no longer activated. The parent checkbox shows a check but the child checkboxes only activate when the user unchecks and rechecks the parent checkbox.


For example:


Checkbox1 (parent checkbox) when checked allows user to enter ReportTitle1 (child textbox) and activates 5 other check boxes (child checkboxes 2-6.) Suppose the user checks Checkbox1 which activates ReportTitle1 and Check boxes 2-6 and the user enters text in ReportTitle1 checks check box 3 and 5. When the user reactives the userform, Checkbox1 is checked, however, ReportTitle1 and check boxes 2-6 are not activated but check box 3 and 5 are checked.


In the above scenario would like the userform (upon re-opening)to automatically activate ReportTitle1 and check boxes 2-6 with the retained values.


Is this possible?


Thanks
 
Hi, txfrazier!


Addressing your 1st paragraph's problem is easy: consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Let me see if without the file I could help you:


You say "when the user re-opens the form". User forms don't have the open method, they can be:

a) loaded and unloaded with the VB instructions Load & Unload

b) displayed or hidden with the methods Show & Hide

c) activated or deactivated and trigger the events Activate & Deactivate

... but nothing about opening and closing them, so please clarify what did you try to mean and/or post here the related code.


Regards!
 
SirJB7,


Thanks. The spreadsheet/userform I'm using has a lot of proprietary info so I can't upload a sample file that would illustrate the problem I'm having. Is there a way to upload the image as a jpeg? As far as the method - I'm using the Show & Hide method for the userform.


Regards,
 
Hi Frazier ,


Try using a Userform_Activate event to initialize your userform ; check out these two links :


http://www.ozgrid.com/forum/showthread.php?t=29803


http://www.ozgrid.com/Excel/free-training/ExcelVBA2/excelvba2lesson4.htm


Narayan
 
Hi, txfrazier!


Give a look at the third green sticky post of the 4 that are at this forums main page for uploading guidelines:

http://chandoo.org/forums/topic/posting-a-sample-workbook


If you can't manage to handle dummy data, maybe you can upload text files with the worksheets, modules and userforms code (from the VBA editor. Alt-F11)? Because if I'm not wrong our issue is much related to what and where is done something with the userform and not with its design, content or aspect. Despite of this you could upload the images that you consider would help.


Regards!
 
I think you could do this.


1. Write all default status code in Userform_Initialize event.

2. Once done with the reporting code at the end of it call back this Userform_Initialize which will reset.

e.g.

[pre]
Code: 
Private Sub Userform_Initialize()
'All Default settings
Me.CheckBox1.Value = 1
End Sub
Then in the report code.

Sub DoReporting()
'Report code...
Call Userform_Initialize
End Sub
[/pre]
 
SirJB7 and others,


Thanks for all your help. Still searching for a solution. Here's a link to a "shell" file that should have. I hope this helps.


https://skydrive.live.com/redir.aspx?cid=fe046114d24ad196&page=view&resid=FE046114D24AD196%21391&parid=FE046114D24AD196%21134&authkey=%21&Bpub=SDX.SkyDrive&Bsrc=Share
 
Hi, txfrazier!

The link to skydrive isn't working, either a wrong link, the file is deleted, unavailable or you havent granted public access.

Regards!
 
Hi, txfrazier!

Same issue. If using skydrive please check link from other computer or from same previously closing any session with hotmail/outlook/live or other MS accounts (mail, messenger, skype,...) before posting. Thank you.

Regards!
 
Hopefully, third time is a charm! I hink the previous link did not include the "131" at then end when you cliked on the link.


https://skydrive.live.com/?cid=fe046114d24ad196&id=FE046114D24AD196%21131
 
Hi, txfrazier!

Sorry, I'm unable to download your file from skydrive. It reports as damaged and after repair neither userforms nor vba code inside. Use another cloud service and test it by yourself (disconnected from related accounts) and don't open it as WebApp but as Desktop.

Regards!
 
Hi Pablo ,


The last posted link is working correctly.


https://skydrive.live.com/?cid=fe046114d24ad196&id=FE046114D24AD196%21131


Narayan
 
Still looking for help with my userform display. It looks like the last link worked according to Narayank991.


Thanks
 
Hi, txfrazier!

Sorry, I apologize, I forget coming back to this topic. I'll give a look at it now.

Regards!
 
Hi, txfrazier!


The link leads me to SkyDrive where there are 2 files:

Userform Sample - Editable, which has no user forms.

Userform Sample, which gives an error when opened after being downloaded, check the uploaded image file:

https://dl.dropboxusercontent.com/u/60558749/Userform%20not%20displaying%20properly%20-%20Userform%20sample%20%28for%20txfrazier%20at%20chandoo.org%29.png


First, an error path or file error access, error 75 specified object not found: the frmReports user form. Then Excel crashes and when restarted manually or automatically, the recovered file has a user form with that name. Hope to be the right one and without any damage.


It'd be helpful if you upload a file fully working and downloadable (i.e., not for being opened at the cloud with Excel WebApp. In the meanwhile I'll be giving a look at my copy.


Regards!
 
Hi, txfrazier!


As far as I could see with the constraint for obvious reasons of not having the full workbook, after checking the checkboxes and filling the textboxes that you gave in your upwards example, when you press the OK button on the form this code is executed:

-----

[pre]
Code: 
Private Sub cmdOK_Click()
Unload Me
Sheets("ERR Illustration").Select
Range("A1").Select
End Sub
-----


Which is the first thing done? Unloading the user form, so losing all values entered on it. That's the answer to my first asked question a) at http://chandoo.org/forums/topic/userform-not-displaying-properly#post-99337.


I manually run the the user form because I didn't find where it was launched from apart from the DesignReports sub; this for the 1st time. After crashing the code when not found worksheet "ERR Illustration" I should have to manually get the form displayed again and as it should I get the form controls values as they were set in design mode.


You have 2 workarounds for this issue:


1) easy way


a) replace the Unload instructions by the method Hide:

Unload Me

by

Me.Hide

b) replace the Load instruction (if any) + the method Show by an Initialize event + the method Show:

Load frmReports

frmReports.Show

by

frmReports.Show

and code like this in the user form:

-----

[pre][code]Private Sub UserForm_Initialize()
' place here the code required prior to get the form displayed 1st time, if any
End Sub
-----

So when getting the form displayed again it'll keep the previous values for its controls as it haven't been unloaded.


2) hard way

If for any reason you couldn't proceed as 1) a) & b) then:


a) replace each Unload instruction by a save control values action + Unload instruction:

Unload Me

by:

SaveControlValues

Unload Me

where the code under procedure sub SaveControlValues (in any module) should look like:

-----

Sub SaveControlValues()
' place here the code required prior to save all control values to a helper worksheet
With Worksheets("XXX")
.Cells(1, 1).Value = chkReport1.Value
.Cells(2, 1).Value = txtTitle1.Value
'...
End With
End Sub
[/pre]
-----


b) replace the Load instruction (if any) + the method Show by the instruction Load + a GetControlValues sub procedure call + the method Show:

Load frmReports

frmReports.Show

by

Load frmReports

GetControlValues

frmReports.Show

where the code under procedure sub SaveControlValues (in any module) should look like:

-----

Sub GetControlValues()
' place here the code required prior to save all control values to a helper worksheet
With Worksheets("XXX")
chkReport1.Value = .Cells(1, 1).Value
txtTitle1.Value = .Cells(2, 1).Value
'...
End With
End Sub[/code][/pre]
-----


Hope I have explained myself clearly, if not just advise if any issue.


Regards!


PS: If any other information from the workbook would be needed for further assistance, get sure that the uploaded file works properly as described and requested ut-supra.
 
You must log in or register to reply here.
Back
Top