Creating, initializing and using a dialog in VBA

[Joseph M. Newcomer]

If I were creating a dialog that needs to return an option value, in C++/MFC I might do

UINT result = dlg.DoModal();

where dlg was my dialog template. I would do an EndDialog(n) to return value n.

In VBA, I want to do this. I can't figure out how to set the initial one of three radio
buttons (OptionButtons) [I am amazed that, given the quality of the documentation, anyone
is able to accomplish anything], and return a set of values, for example
0 - cancel was clicked
1 - OK, option button 1 selected
2 - OK, option button 2 selected
3 - OK, option button 3 selected

I have an additional problem with this; I want the form to be represented ENTIRELY as a
text file, no funky form pictures. I need to be able to give the end users a text file
they can paste in to their project from the clipboard, and this does not seem to be
possible, either.

Pointers to example code would help. Also, is there any form of decent documentation for
VBA? Or should I be considering a .NET language like C# for this purpose? If so, what
newsgroup should I be in?
joe
Joseph M. Newcomer [MVP]
email: (e-mail address removed)
Web: http://www.flounder.com
MVP Tips: http://www.flounder.com/mvp_tips.htm

 

[Steve Rindsberg]

If I were creating a dialog that needs to return an option value, in C++/MFC I might do

UINT result = dlg.DoModal();

where dlg was my dialog template. I would do an EndDialog(n) to return value n.

To set values within the form, you'd use code in the form's Activate event.
Forms don't return values. Instead, you can let code in the form set values in public
variables.

Or probably better all around, you can set and get values in the form as long as the form
is loaded (it needn't be visible). While frmSomething is loaded, you can e.g.

frm.txtTextBox1.Text = "Some text"
or
Debug.Print frm.txtTextBox1.Text

I have an additional problem with this; I want the form to be represented ENTIRELY as a
text file, no funky form pictures. I need to be able to give the end users a text file
they can paste in to their project from the clipboard, and this does not seem to be
possible, either.

Not that I know of, but there's lots that I don't know of. But depending on your needs the
form might be pre-populated with some number of invisible controls which you make visible
and position as needed based on input from the user's text/code. Or add the controls on
the fly.

Pointers to example code would help. Also, is there any form of decent documentation for
VBA?

For all practical purposes, VBA = VB + Application-specific extensions.
What you get in the help files and MSDN tends to be reference material, handy if you need
to look up the syntax for something you already know how to do but very useful if you're
new on the block. In that case, VB6 tutorials or books would be the best place to look.

 

[Joseph M. Newcomer]

Thanks. Too bad they didn't hire professionals to write the documentation, but I guess
we're stuck with the primitive documentation they provide (I'm used to the professional
quality documentation of products like Word, PowerPoint, Visual Studio, etc.).

Is there a way to create a dialog and populate it with buttons, entirely from VBA code?
That's what I'm looking for.
joe

To set values within the form, you'd use code in the form's Activate event.
Forms don't return values. Instead, you can let code in the form set values in public
variables.

Or probably better all around, you can set and get values in the form as long as the form
is loaded (it needn't be visible). While frmSomething is loaded, you can e.g.

frm.txtTextBox1.Text = "Some text"
or
Debug.Print frm.txtTextBox1.Text


Not that I know of, but there's lots that I don't know of. But depending on your needs the
form might be pre-populated with some number of invisible controls which you make visible
and position as needed based on input from the user's text/code. Or add the controls on
the fly.



For all practical purposes, VBA = VB + Application-specific extensions.
What you get in the help files and MSDN tends to be reference material, handy if you need
to look up the syntax for something you already know how to do but very useful if you're
new on the block. In that case, VB6 tutorials or books would be the best place to look.

Joseph M. Newcomer [MVP]
email: (e-mail address removed)
Web: http://www.flounder.com
MVP Tips: http://www.flounder.com/mvp_tips.htm

 

[Steve Rindsberg]

Thanks. Too bad they didn't hire professionals to write the documentation, but I guess
we're stuck with the primitive documentation they provide (I'm used to the professional
quality documentation of products like Word, PowerPoint, Visual Studio, etc.).

Is there a way to create a dialog and populate it with buttons, entirely from VBA code?
That's what I'm looking for.

Have a look at help on the Controls Collection (for forms)
(Look here for some help files that'll be more useful than the mess provided with 2007.)

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\1033

I just did a bit of playing with Controls.Add and was able to add new controls to the form in
the form's Activate event, wasn't sure how to do it from external code, but an external module
could pass the needed info as a public array or the like.

Setting it all up from scratch would probably require that your code address the VBA object
model. I've never done this so can't offer any useful suggestions.

 

[Joseph M. Newcomer]

Irony? We had it here back in 1955, but it didn't catch on...

(paraphrase of Steve Martin in the movie "Roxanne")

I gave up, and threw out the whole notion of the VBA script entirely; now I use the
Automation Interface from MFC to get the data I want. So I don't need to supply the end
user with a VBA interface any longer (it's a bit slower, but a lot easier on the end
users; they only have to open the PPT file to get the indexing done)

The VBA documentation is coming close to Unix 'man' pages for total unusability.
joe

Have a look at help on the Controls Collection (for forms)
(Look here for some help files that'll be more useful than the mess provided with 2007.)

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\1033

I just did a bit of playing with Controls.Add and was able to add new controls to the form in
the form's Activate event, wasn't sure how to do it from external code, but an external module
could pass the needed info as a public array or the like.

Setting it all up from scratch would probably require that your code address the VBA object
model. I've never done this so can't offer any useful suggestions.

Joseph M. Newcomer [MVP]
email: (e-mail address removed)
Web: http://www.flounder.com
MVP Tips: http://www.flounder.com/mvp_tips.htm

 

[Steve Rindsberg]

Irony? We had it here back in 1955, but it didn't catch on...

The VBA documentation is coming close to Unix 'man' pages for total unusability.

Give them time. They'll improve 'em to where they surpass man pages in that department, I'm sure.