vba excel - loading sheet data into userform using IsEmpty

[terriertrip]

I'm pretty sure this is basic, but I have a form storing 1 row of data (Table[A2:X2]) to use as a reference for my entire project. The form forces all controls filled before moving data with only option to fill or close workbook. Once data is stored, worksheet change event dates "Y2". The controls need populate only if IsEmpty check on "Y2" is true. Below is my working code, but I'm getting an invalid property value error on form initialization when the table is filled. What am I missing here? Here's what I've been working on but keep getting compile errors.

Please Login or Register  to view this content.

[mjr veverka]

Try with

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Norie]

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

[terriertrip]

Hi Norie. I've uploaded a parsed down version so it doesn't have the worksheet change event code in the sheet, but if you just put a value in C2 for now to mimic the desired result, that should be enough.

[terriertrip]

workbook is attached.

[terriertrip]

porucha vevrku. that seems a bit over thought to me but I'm very new to vba too. It's a form initialization problem. I'm not having any issues with calling the data sources up or once filled, moving data from the form to the sheet - just when I introduce a procedure calling the data back up (if not empty) if it is there.

EDIT: I've also been able to call the data up with other code I've tested, but then when the range is empty, it errors, so it either works one way or the other and I want it to work both ways.

[mjr veverka]

1. What is your original filename of WorkBook (name) ?
You uploaded test.xlsm but in vba code is "msBOOKS"

2. Error in the definition formula of "State"
Give up of use Offset and use instead:

Please Login or Register  to view this content.

in this case

Please Login or Register  to view this content.

is no needed

[Norie]

All of the defined names in the workbook are invalid so can't really do much with the workbook - the userform's Initialize event won't even run.

[terriertrip]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

sorry about the confusion

[Norie]

When I make those changes the userform seems to work just fine, the combobox populates as expected and values are saved then the Create button is clicked.

What exactly is the problem you are having and how can I recreate it?

[terriertrip]

Sorry about that. I was in a rush, so here's a corrected version.

[terriertrip]

wb attached. The forum keeps wanting me to log in every time I navigate anywhere.

[terriertrip]

when I initialize the form again after unloading, the values are not in the control boxes. I want the values to populate the controls (if not empty).

[Norie]

What values would you expect to see in the userform?

PS What is the Application.CountA here meant to be checking?

Please Login or Register  to view this content.

[terriertrip]

Whatever is entered and saved. I'm thinking the Application.CountA is redundant and in fact probably don't need it since IsEmpty is checking to see if C2 is filled and if is filled, the userform calls the range values.

[Norie]

This will populate the combobox and put the values from row 2 from Sheet2 on the userform.

Please Login or Register  to view this content.

[terriertrip]

Thank you Norie. Much more concise. Works as intended.