CheckBox

[Jenna]

Hi
I have created a userform that loads on "Workbook_Open", where i inform all users of the changes i've made, and how to work with my new list.

I want to be able to let the other users to prevent the userform from autoload on start by "Unchecking" a "Checkbox". I've added a "CheckBox", value set to "True", to the Userform. But i can't figure out how to change the value to "false" by "Unchecking" it, and how to save the changes. And i'm not sure how to prevent it from load on"Workbook_Open" when the value is set to False

I'm sure that this is possible to do, but all i have tried have failed so far...

Suggestions?

/Jenna

 

[bjwillingham]

Your problem is in saving the checkbox state separate from the check box, which at startup will always contain the value set at design time.

Creat a name such as "NewView" in the worksheet and set = TRUE.

Then, in the userform_initialize routine set the state of the check box to whatever NewView is equal to. Then let the initialize routine decide whether to show the dialog box. When the dialog box is shown and the user checks or unchecks the box, have the checkbox_change routine update NewView. The value of NewView will be saved with the spreadsheet.

You could even define a custom property, which would be better, but more complicated.

I hope this makes sense.

 

[Jenna]

Hi

Thanks for your reply!

I've been trying to do what you suggested, but i can't get the "NewView" value to change. I've tried to change it by the "CheckBox_Change" and "CheckBox_Click" event, but it haven't worked so far...

Could you be more specific?

/Jenna

 

[bjwillingham]

I apologize. The name thing doesn't seem to work like I thought.


Create a custom document property called "newview". Set it to a yes/no value. Set the value to 'no'.

ActiveWorkbook.CustomDocumentProperties("newview").value will evaluate to false for no and true for yes.

for your document autoopen code:

Private Sub Workbook_Open()
If ActiveWorkbook.CustomDocumentProperties("newview").Value = False Then
Load UserForm1
UserForm1.Show
End If
End Sub

In the user form initialize code put:

Private Sub UserForm1_Initialize()
Dim tf As Boolean
tf = ActiveWorkbook.CustomDocumentProperties("Newview")
cbNewview.Value = tf

End Sub

For the checkbox put:

Private Sub cbNewview_Change()
If cbNewview.Value = True Then
ActiveWorkbook.CustomDocumentProperties("newview").Value = True
Else
ActiveWorkbook.CustomDocumentProperties("newview").Value = False
End If
End Sub

Have an OK command button set the cancel yes and the code:

Sub cbok_click()
UserForm1.Hide
End Sub

 

[Jenna]

Hi bjwillingham!

A big Thank You for even bothering to try explaining this to me again!

I'm the one who should apologize, not you!
I should of course have told from the beginning that i'm so much of a "Newbie" that's possible. And that i have never ever written, changed or even seen vba code until this week. So am totally stuck here, and to tell you the truth, i really don't have a clue of what some of the code are supposed to do! I have an excelbased productlist (made by an x-boyfriend), and found this board earlier this week when the userform that was used for "input data" failed.

With a great deal of assistance from MartinK (Thanks Again), i've skipped the "input data" userform and modyfied my list, and i must admit that it works much better now. When i was about to "delete" my userform i've got the idea of having it work to inform all other user's of the changes that have been made, and how to work with the "new list".

I have copied/pasted your code to "Private Sub Workbook_Open()" and to the "Private Sub UserForm1_Initialize()", and the code for "Private Sub cbNewview_Change()" right below the "Private Sub UserForm1_Initialize". I think it's right so far, but:
I'm not sure that i created the"CustomDocumentProperty" value right, should it be named like a "Sub CustomDocumentProperty / Sub newview" or like"CustomDocumentProperty / newview"?
Should it be in the "UserForm", "Sheet1(Sheet1)" or "This Workbook"?
Where am i supposed to put:
Sub cbok_click()
UserForm1.Hide
End Sub
Should this be placed within the "UserForm" and for what purpose, i already have a close button?

I realise that my questions look's quite stupid to most of the board members (this really brings back the "first day in school" feeling ), but maybe they are good for a laugh or two??

/Jenna