Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / April 2007

Tip: Looking for answers? Try searching our database.

Run routine when the Userform is in the active state

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
frank - 12 Apr 2007 03:00 GMT
I have a non-modal userform which allows the user to select back and forth
between the document and the userform.  There is a list box where the user
can select different items.  

Every time the user clicks on the document, the userform loses focus and
when the user clicks back to the userform, I need to run a refresh routine
that I created.  It's a routine that will update the items in the listbox.  

I tried using Private Sub UserForm_Activate().  That only seems to run when
the Userform is called out the first time (userform.show).  I need this
routine to run every time the user moves the focus from the document to the
userform.  

I tried using the  Private Sub UserForm_Click() procedure but if the user
selected something in the listbox, then selects the document, then moves the
focus back to the userform by clicking on an item in the same listbox,
nothing happens and I can't use the click procedure on the listbox to run the
routine because the routine resets the listbox which would prevent the user
from being able to select different items in the box.

Sorry for the long explanation, but most people seem to criticize for not
having enough information rather than too much.

Any help is greatly appreciated.

Thanks,
Jean-Guy Marcil - 12 Apr 2007 05:23 GMT
frank was telling us:
frank nous racontait que :

> I have a non-modal userform which allows the user to select back and
> forth between the document and the userform.  There is a list box
[quoted text clipped - 22 lines]
>
> Any help is greatly appreciated.

I am not sure you can do that in VBA.
A traditional Form object in VB6 has a GotFocus event (Replaced by the
"Enter" event in .Net) that you could use.

Instead of a modeless userform, you could use a toolbar menu that gives
access to a combobox control. You could have code that updates the combobox
content when the user clicks on the toolbar menu.

Here's a similar example. Create a dummy template to see how it works. This
example uses a toolbar button to update a toolbar combobox next to it.

'_______________________________________
Option Explicit

'Change the caption constant to suit your situation
Public Const ButtonCaption As String = "Heading 1 list"
Public Const ToolBarName As String = "Test"
'_______________________________________
Sub AddComboBox()
'With the template open, create a toolbar, call it "Test"
'(If you want a different name, call it something else
'and do not forget to change it as well in the constant
'declaration above so that all macros will work)
'Add the macro "FillHeadingList" below to the toolbar
'as the first button
'Tools > Customize, Commands tab, on the right select Macros
'On the left find the macro and drag it to the toolbar
'Right click on it and in the name area type: Update
'Then from VBA IDE, place the cursor in this text and press F5
'to execute this macro (AddComboBox)
'Finally, if you want, in the ThisDocument module,
'Create a Document_Open event
'Add to that event: DropList.FillHeadingList
'and a Document_New event if necessary as well
'So everytime the document will be opeend the list will
'update itself as well as when the user clicks on the
'Update button on the Test toolbar

Dim cbo As Office.CommandBarComboBox

   Set cbo = CommandBars(ToolBarName). _
       Controls.Add(Type:=msoControlComboBox)

   cbo.Caption = ButtonCaption
'when user selects an item in the list,
'this macro will execute:
   cbo.OnAction = "DropList.GoToHeading"
   cbo.Width = 100

End Sub
'_______________________________________

'_______________________________________
Sub ResetHeadingList()

   CommandBars(ToolBarName). _
       Controls(ButtonCaption).Clear

End Sub
'_______________________________________

'_______________________________________
Sub FillHeadingList()

Dim cbo As Office.CommandBarComboBox
Dim bFound As Boolean
Dim CursorPos As Range

  DropList.ResetHeadingList

  Selection.Collapse wdCollapseStart

  Set CursorPos = Selection.Range

  Set cbo = CommandBars(ToolBarName). _
   Controls(ButtonCaption)
  Selection.HomeKey Unit:=wdStory
  Selection.Find.ClearFormatting
  Do
  With Selection.Find
       .Format = True
       .Style = wdStyleHeading1
       .Forward = True
       .Wrap = wdFindStop
       .Text = ""
       .Execute
       bFound = .Found
       If bFound Then cbo.AddItem Left(Selection.Text, _
           Len(Selection.Text) - 1)
       Selection.Collapse wdCollapseEnd
  End With
'In case last para is Heading 1, exit loop
  Loop While bFound And _
   Selection.End + 1 < ActiveDocument.Range.End
'Instead of a Do...Loop, you could have a bunch of
' cbo.AddItem staement to fill your bar with static names
'In this case, you do not need the Update button or
'the Document_Open event
'Just run this macros from the VBA IDE once

  CursorPos.Select

End Sub
'_______________________________________

'_______________________________________
Sub GoToHeading()

   Dim cbo As Office.CommandBarComboBox
   Dim rng As Word.Range
   Set cbo = CommandBars(ToolBarName). _
       Controls(ButtonCaption)
   Set rng = ActiveDocument.Range

   With rng.Find
       .Style = wdStyleHeading1
       .Text = cbo.Text
       .Execute
   End With
   rng.Select
'or something like:
'    Selection.TypeText cbo.Text
   Selection.Collapse wdCollapseStart
End Sub
'_______________________________________

But, it could probably be done so that the combobox is a sub-item to the
first menu. Look at the following example. the problem is that toolbar
comboboxes do not behave too well when they are in a sub menu... Maybe
someone will have a solution if you want to go that way...

'_______________________________________
Option Explicit

Public Const ToolBarName As String = "Test"
'_______________________________________
Sub AddComboBox()

Dim menuBut As Office.CommandBarControl
Dim cbo As Office.CommandBarComboBox

   Set menuBut = CommandBars(ToolBarName). _
       Controls.Add(Type:=msoControlPopup)
   With menuBut
       .Caption = "List"
       Set cbo = CommandBars(ToolBarName) _
           .Controls(1).CommandBar.Controls.Add(Type:=msoControlComboBox)
           .OnAction = "DropList.FillHeadingList"
       With cbo
           'when user selects an item in the list,
           'this macro will execute:
           .OnAction = "DropList.GoToHeading"
           .Width = 150
       End With
   End With

End Sub
'_______________________________________

'_______________________________________
Sub ResetHeadingList()

   CommandBars(ToolBarName). _
       Controls(1).CommandBar.Controls(1).Clear

End Sub
'_______________________________________

'_______________________________________
Sub FillHeadingList()

Dim cbo As Office.CommandBarComboBox
Dim bFound As Boolean
Dim CursorPos As Range

  DropList.ResetHeadingList

  Selection.Collapse wdCollapseStart

  Set CursorPos = Selection.Range

  Set cbo = CommandBars(ToolBarName). _
       Controls(1).CommandBar.Controls(1)
  Selection.HomeKey Unit:=wdStory
  Selection.Find.ClearFormatting
  Do
  With Selection.Find
       .Format = True
       .Style = wdStyleHeading1
       .Forward = True
       .Wrap = wdFindStop
       .Text = ""
       .Execute
       bFound = .Found
       If bFound Then cbo.AddItem Left(Selection.Text, _
           Len(Selection.Text) - 1)
       Selection.Collapse wdCollapseEnd
  End With
'In case last para is Heading 1, exit loop
  Loop While bFound And _
   Selection.End + 1 < ActiveDocument.Range.End
'Instead of a Do...Loop, you could have a bunch of
' cbo.AddItem staement to fill your bar with static names
'In this case, you do not need the Update button or
'the Document_Open event
'Just run this macros from the VBA IDE once

  CursorPos.Select

End Sub
'_______________________________________

'_______________________________________
Sub GoToHeading()

   Dim cbo As Office.CommandBarComboBox
   Dim rng As Word.Range
   Set cbo = CommandBars(ToolBarName). _
       Controls(1).CommandBar.Controls(1)
   Set rng = ActiveDocument.Range

   With rng.Find
       .Style = wdStyleHeading1
       .Text = cbo.Text
       .Execute
   End With
   rng.Select
'or something like:
'    Selection.TypeText cbo.Text
   Selection.Collapse wdCollapseStart
End Sub
'_______________________________________

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

frank - 13 Apr 2007 00:30 GMT
Sorry for the late response but I think there was something wrong with the
MSDN website.  When I tried to reply, I kept getting browser errors.

Thank you for this coding.  This is great.

I have one other small issue.  What's the easiest way to check for a
duplicate value in an Array to ensure each value input into the Array is
unique?

Thanks,

> frank was telling us:
> frank nous racontait que :
[quoted text clipped - 258 lines]
> End Sub
> '_______________________________________
Jean-Guy Marcil - 13 Apr 2007 03:10 GMT
frank was telling us:
frank nous racontait que :

> Sorry for the late response but I think there was something wrong
> with the MSDN website.  When I tried to reply, I kept getting browser
[quoted text clipped - 5 lines]
> duplicate value in an Array to ensure each value input into the Array
> is unique?

Basically, you sort the array, then check each item with the next one, if it
is identical, remove the next item.

Do a Google in the word.vba group using "duplicate", "array" and "sort" as
key words and you will get tons of coding examples.

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

frank - 13 Apr 2007 20:10 GMT
That's great!  thanks again for everything!

> frank was telling us:
> frank nous racontait que :
[quoted text clipped - 14 lines]
> Do a Google in the word.vba group using "duplicate", "array" and "sort" as
> key words and you will get tons of coding examples.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.