first word vba attempt

[allison]

Just need a bit of direction, if possible.

I am trying to write something that everyone in our department can use - multiple people simulatenously. All people have network access to where the information is.

I have a document that has 1 page per state. Right now, I have some VBA that brings up a dialog box and you enter the state name and it goes to that page.

What I would like to do is this:

1. Have someone open a document called "Search".
2. Have the dialog box open automatically. User will enter the state name.
3. Behind the scenes, the main document is opened, the state page is found, copy/pasted into the search document and then closed.

Here are my questions:
1. Is that possible?
2. How do you a dialog box to open when a document is opened?

Thanks!

3.

[OTWarrior]

It should be possible, and off the top of my head autoexec is what you need.

this thread may help:

http://www.vbaexpress.com/forum/showthread.php?t=11047&highlight=autoexec


If you want the user to enter the state name, you have to be careful with what they enter, as there can be many permutations to the same item of data.

for example

New York
Newyork
new york
NY
N.Y

Unless the users are going to use the same format, you may have headaches getting this to work. Although the command you would need for this anyway is called InputBox

eg:

If input("Please enter a state","Enter State Please") = "New York" then
'your code
end if


in your example, having it as a case statement would be alot better however :D

Let me know if that helps you at all.

[allison]

Either I didn't understand, it didn't work, or I didn't explain well enough what I wanted. I'm sure that it has something to do with me though!

I've tried the input box code in an autoexec subroutine in both of these places (at different times to test it)

Normal/ThisDocument
Normal/NewMacros

Neither of which automatically pull up the input box. I can get the input box by running the macro, but I need to avoid that.

[Tinbendr]

Take a look at the attached files.

[fumei]

Here are my questions:
1. Is that possible?
2. How do you a dialog box to open when a document is opened?

1. Yes, absolutely.
2. Document_Open

Document_Open is a built-in event. You can find it in the ThisDocument module. Click the left dropdown at the top, select Document. The default procedure is Document_New. Either change "New" to "Open", or click the right dropdown at the top, and select Open.

Anything in Document_Open executes, ummm, when the document is opened.

OTWarrior is quite correct that you can have a headache trying to make sure you get proper text for a state. So....do not do it as a user typed choice. Do it as a user selected choice, via a userform.

The userform displays on opening the document using Document_Open), and lists the states in a combobox. You could also use a listbox. The list is populated when the userform is initialized, like this:
Private Sub UserForm_Initialize()
Dim States()
Dim var
States = Array("Alabama", "New York", "Maine", _
"Arizona", "Texas", "Yadda", _
"Blah", "MoreBlah", "North Dakota", _
"California")
For var = 0 To UBound(States)
cboStates.AddItem States(var)
Next
End Sub

This way, there is no room for errors. No newyork, or New york, or n.Y, or N Y. They simply select the state, and click Insert (or whatever term you wish).

The Insert button then takes the user selected state and does its thing with the other document.

BTW: I would recommend having that source document (with the states pages) bookmarked. That way if you ever expand the range of content for each states beyond a page, it will not matter any difference at all. You simply grab the "State" - whatever its length/content is.

Note though that bookmarks do NOT allow spaces. So you would have to convert "New York" as a selected item to "NewYork" is that was the bookmark name. Here is a sample possibility of the code behind an Insert button.
Dim State As String
Dim ThisDoc As Document
Dim SourceDoc As Document
' make string variable of the selected State - no spaces
State = Replace(cboStates.Text, " ", "")
Application.ScreenUpdating = False
' make Doc variable of current doc
Set ThisDoc = ActiveDocument
' make doc variable of Source
Set SourceDoc = Documents.Open(FileName:="your document path")
' make current doc = the range of the selected item
ThisDoc.Range = SourceDoc.Bookmarks(State).Range

Done.


User selects Texas, clicks Insert, Source doc opened, and current doc contents becomes the range of "Texas".

Pun intended.

Sample/demo attached. Note the combobox starts off blank. Normally I do not do that, but in this case, as you are going to have a long list, you may want to take advantage of the AutoWordSelect. If you type "c", it will jump to the first "c" (in this case California).

Note I did NOT type in all the states for you! Just show you how you would. BTW: if you use States listings often, keep this array.

[fumei]

Did not see Tinbendr's post.

I would recommend NOT making the displayed userform fill up like that. It could very easily become not fully visible, depending on user screen resolution. It is bad form to force a user to move something just to be able to see a required button. On my system I can not see the OK button, which mean unless you know (and expect that will be one), you will not know.

[allison]

I appreciate the suggestions and the getmetexas document. Thanks!!

I do have one more question though...as I was looking through the getmetexas document, I was looking for where the combo box that you created was getting the names. I could not find the initialize routine where you listed the states. I probably am blind or something - but could you pls point me in the correct direction?

[fumei]

It is in the code module for the userform, where it has to be, as it is Initialize.



Private Sub UserForm_Initialize()
Dim States()
Dim var
States = Array("Alabama", "New York", "Maine", _
"Arizona", "Texas", "Yadda", _
"Blah", "MoreBlah", "North Dakota", _
"California")
For var = 0 To UBound(States)
cboStates.AddItem States(var)
Next
End Sub[[
Actually, this is poor code, as there is a much better way that does not use a Loop.


Private Sub UserForm_Initialize()
Dim States()
Dim var
States = Array("Alabama", "New York", "Maine", _
"Arizona", "Texas", "Yadda", _
"Blah", "MoreBlah", "North Dakota", _
"California")

cboStates.List = States()

End Sub