Check for default values, Prompt for default values, Double click change default valu

**Boxer** · 11-17-2008, 05:05 PM

I have a Userform with 7 TextBoxes, TextBox1, TextBox2a, TextBox2b, TextBox3a, TextBox3b, TextBox4a, and TextBox4b.

As the Userform opens I would like it to check if all 7 TextBoxes have a value. The corresponding cells are in a worksheet named “Income 1 Data” and are as follows:

TextBox1 = Cell A2 and has a Text value
TextBox2a = Cell C2 and has a Number value
TextBox2b = Cell G2 and has a Percentage value
TextBox3a = Cell D2 and has a Number value
TextBox3b = Cell H2 and has a Percentage value
TextBox4a = Cell E2 and has a Number value
TextBox4b = Cell I2 and has a Percentage value

If any (one or more) of the cells have not been assigned a value I would like a prompt to appear stating “Not all default values have not been declared. Do you want to set default values?”

If yes, Userform to input values, if no, then cancel default values and accept user input from main Userform. I think it would be too complicated to state which defaults have not been declared unless it is possible to sate it within the MessageBox. For example if Cell A2 and C2 have not been assigned a value: “Taxable Marital Status, Federal Exemption default values have not been declared. Do you want to set default values?”

If the user chooses to ignore the Message by canceling it, then have the tab stop set to TextBox1 then the next, Etc. If all the required cells have values then tab to the next TextBox in the Userform (TextBox5).

If a user wishes to change the default value by way of the Userform through a Doubleclik event in the corresponding TextBox let it be so, but upon closure of the Userform have a MessageBox appear stating “Some default values have been changed. Do you want to save changes (Yes, No). Again I think it would be too complicated to declare which values have been changed.

I can’t seem to get my head around the required looping and conditioning. I can really use some help with this.

Thanks

**Kenneth Hobson** · 11-17-2008, 05:33 PM

There are 4 ways that I can think of to do it. If you only have those textboxes on the userform, a class could be use. The Tag property for the textboxes could be used. You could check each as focus was lost. Or a loop like this:

Please Login or Register  to view this content.

You can change the c.Name in the MsgBox to say c.ControlTipText if you want to add a descriptive name for your textbox for the message. Of course you would need to change TextBox1 to the name of your textbox and add other Case lines for each of the default values that you want to insert if they click Yes.

**Boxer** · 11-17-2008, 09:14 PM

To tell you the truth I don’t understand what you have posted. Will this evaluate the cells when the form is opened? Does it evaluate the data in the cells or the textbox? I’m not sure if I explained what I am looking for properly.

There are quite a few other Textboxes on the Userform requiring input from a user. Please reread my post and if I wasn’t clear I will try to explain better. Please see the zip file I have attached.