Creating custom Word command bar - click event

[swamiandy]

Hello,

I am in the process of developing a Word add-in that provides a custom command bar to the user. The command bar consists of two commandbarcombobox controls of type "msoControlComboBox" and a button.

The commandbar should only be enabled when a selection has been made in the document (how to do this?). Once a selection has been made, the user can enter text into both boxes, and then he presses the submit button. This sends the selection information (startChar, endChar, wordCount, docFilename) and the user-input text strings, to an excel spreadsheet (via ADODB).

My problem right now is that when I enter text into the first box and then click on the second box, the text in the first box disappears. However when I use the tab button to move from the first box to the second, the text stays in the box. But then - even if delete the contents, and click on something else. the old text reappears in the box again.

Essentially - clicking away causes the value to disappear, but tabbing away causes it to get written to the .text property of the control.

Why this difference between click and tab - and how to get this thing working properly? (I have tried defined .onAction subs for both boxes - but they don't get executed when I click on the boxes??)

If anyone could help me out I'd be very grateful. I can post my code modules if anybody's interested...

Kind Regards

Andy

[swamiandy]

...heres the add-in (as a .dot) file

andy

[lucas]

....hmm, sounds like your trying to use a commandbar drop down to do what you should be using a userform or input box for....?
can you post the workbook...use manage attachments below your post after you click on post reply...

[fumei]

Sorry, but...bleeeech. Why are you doing things this way?

1. Comboboxes are for listing items. Yes, it is true you can enter text into comboboxes, but that is not what they are for. Having a combobox with an empty dropdown listing is, well, it seems strange to me.

2. If I understand things, you simply want:

the text selected
the selection positions
the selection word count
two separate text inputs from the user

A userform is the easiest way to do this. The user selects some text, clicks a button (or uses a keyboard shortcut), gets the userform, enters the text they want, clicks the send button...and you do what you need to to send it to Excel.

Sample file attached. Keyboard shortcut = Alt-G. Or click the "Get Form" icon on the top toolbar. The userform pulls in the selected text and displays it. You may not want, or need, that. The Send to Excel button collects the data (selection positions, word count, text from the two textboxes) and does its thing.

Note: the sample file attached does not have your code to send to Excel.

[fumei]

BTW: your question
The commandbar should only be enabled when a selection has been made in the document (how to do this?). can not be done. You can not really do real time monitoring of the Selection object. Simply because there is ALWAYS a selection made.

Just having the cursor blinking away...there is a Selection object. It is only one character, but it is there. Yes, you can fire a procedure to check and see if it is more than one character - the user has expanded the selection. When are you going to fire it?

You check NOW...nope, one character (user has made no selection).

You check NOW...nope, one character (user has made no selection).

You check NOW...nope, one character (user has made no selection).

You check NOW...nope, one character (user has made no selection).

and on and on??? Don't think so.

Have a way (button, or shortcut) that the user can fire the procdure when they want.

[swamiandy]

Hi Gerry,

Thanks a lot for the advice. I've re-written the add-in using a user form and it's much more appropriate. You have saved me from a lots of pain!:banghead:

I'm still unsure about a couple of things though:

1. How to assign the keyboard shortcut that opens the form?

2. I now have a browse... button at the bottom of the form, which allows the user to specify the folder where the excel metadata file is stored. How to store this value - so that the variable points to the same location, the next time Word is opened? (This will not normally be changed - so I don't want the user to have to keep setting it each time he opens word).

kind regards

Andy

p.s. I've attached the new .dot file

[mdmackillop]

To assign a macro
Tools/Customise/Keyboard/
Save Changes in: MyAddin
Categories: Macros
Macros: GetForm
Shortcut; Alt +G
Assign/Close/Close

[mdmackillop]

Something like this will save/get the value from a text file. You'll need to set initial values though

Private Sub cmdBrowse_Click()
Dim fs, a
Dim fd As FileDialog
Dim folderPicked As String
'Create a FileDialog object as a Folder Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.AllowMultiSelect = False
Dim vrtSelectedItem As Variant
If fd.Show = -1 Then
For Each vrtSelectedItem In fd.SelectedItems
folderPicked = vrtSelectedItem
Next vrtSelectedItem
Else
End If
Me.txtFPath = folderPicked

Kill "c:\FolderPath.txt"
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\FolderPath.txt", True)
a.WriteLine (folderPicked)
a.Close
End Sub

and

Private Sub UserForm_Initialize()
Dim xlfile As String
Dim fs, f

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("c:\FolderPath.txt", 1, 0)
xlfile = f.readline
f.Close
'.........

[fumei]

Or you can save a specific path (or any other string) in a document variable.ActiveDocument.Variables.Add _
Name:="ExcelPath", Value:="C:\Temp\hammer.xls"You retrieve it by its name:ActiveDocument.Variables("ExcelPath")Returns a string - "C:\Temp\hammer.xls"

You can bring that into the textbox on the userform. The user can change it, or not.Textbox1.Text = ActiveDocument.Variables("ExcelPath")

You change the document variable by assigning it another value.ActiveDocument.Variables("ExcelPath").Value = "C:\whatever"
The value is persistent - it is saved with the file. So the next time you load the userform you can easily pick it up.

[swamiandy]

...or I was just thinking... Is there a way of adding a row to the Tools -> Options -> File Locations built-in dialog. That would be a much nicer way of having it.

Thanks again

Andy

[swamiandy]

So, probably that's too much wishful thinking.

the document variable solution seemed to be the more 'elegant' solution, but I would not want to add it to the active document, rather to the attached template (since I'll be working with many different documents).

However, it seems that the only way to add a document variable to the attached template is by using the attachedtemplate.openasdocument method, updating the variable, and then closing and saving the template.

Since I need to check that the variable exists when I initialize the form - that means I am programatically opening and closing the template every time I use the form. (I tested this and it seriously impedes the useability of the form).

So I guess the best solution is to go with the external text file approach. But where is the most suitable place to store this file?

Kind Regards

Andy

[mdmackillop]

Why not save it in the same folder as the template. It doesn't really matter, and it's not a significant file where deletion would cause a big problem.
Two more options.
Save the info in the Registry - http://vbaexpress.com/kb/getarticle.php?kb_id=208
CustomDocumentProperties - see the VBA Help file

[fumei]

the document variable solution seemed to be the more 'elegant' solution, but I would not want to add it to the active document, rather to the attached template (since I'll be working with many different documents).I am not following that.

If I understood correctly, this was a sort of default path to the Excel file. What is the problem with storing it in the document? You could have the SOURCE of the string in the template, if you wanted. Simply have the Document_New event copy the variable that is in the template, a variable in the document.

It HAS to be in the document for it to be retrieved. It is a document variable.

So bring the default string in from the template. Put that default into the textbox on the useform. If the user changes it then you can change the document variable for the document. User opens the document again, the variable will be whatever was the last string they used.

I am not seeing the issue with putting it in the document.

[mdmackillop]

I am not seeing the issue with putting it in the document.
Nor me! :friends:

[swamiandy]

no - I meant the souce of the string...

[fumei]

no - I meant the souce of the string...Huh??? Would you please walk through that for me. I do not understand.

WHAT is the problem with having the string for the path to the Excel file, in the document?