Working with Application in VBA

Out of curiosity, which application are you running the code from?

You could add the data to the other sheet while you are in the UserForm Initialize event. Just add code similar to that you have shown in your second macro that will add the data to the second worksheet. this portion of the code is what I am referring to. No need to go back an re-invent the wheel when you have the data right there.

Please Login or Register  to view this content.

With a little editing, you could probably use the same source reference you use to load the combo box data and label data in the same loop.

Originally Posted by MarBoTJ

Hey, thanks.

Unfortunately, I can't do that, as the userform initializes with only those 2 values and the rest (not shown) must be typed in.

OK, I missed the typed in part in the OP. You did not answer my question about the host file for the code, but based on the fact that your controls may not have complete data at the time you initialize the UserForm, then you would have to have a separate macro to access the data and do the copy/paste. Assuming you are in Access, Power Point, Word or some other MS appliction other than Excel as the host file, you would also need to use the CreateObject methop to get the Excell Application and activate the workbook (your first code does not close the workbook nor quit the Excel Application, so you should be able to just create the Object and use it to activate the workbook and do your stuff. Been a while since I worked across applications so I could be wrong, but it seems logical that it would work.

If you are working only with Excel, then you do not need the CreateObject method to access the application. You are already in the application when you open the first workbook. So you are creating a lot of code that is unnecessary in the procedures in the OP. All you need are the Workbook and Sheet references to get to where you want to access in the files. Understanding the Excel object model is crucial to writing good code.

Originally Posted by MarBoTJ

Yeah, I realized that yesterday, I'm still understanding the application object to know how to manipulate it. I guess that'll do the trick.
I'll close this post, if something else comes up I'll post a new question.

Thanks.

You're welcome,
regards, JLG