AutoNew and Private

[Dan Kelly]

I am trying to get my head around Private declarations and AutoNew/AutoOpen
options.

I have the following code that works:

Option Explicit
Dim cb As CommandBarComboBox

Sub AutoNew()
SetComboBox
End Sub

Private Sub SetComboBox
Set cb = CommandBars("Test").Controls.Add(msoControlComboBox)
with cb
.OnAction = "SetColour"
.AddItem "Green"
' Other setup stuff that works....
End With
End Sub

Private Sub SetColour
Select Case cb.Text
Case "Green"
'Run another Macro that doesn't matter here
EndSub

If however I declare AutoNew as Private so that it is hidden from the Macro
list then the above breaks at the Select Case Statement.

Where am I going wrong?

 

[Jezebel]

Two entirely separate issues.

"Private" means that the declaration is visible only within the module.

Your 'select case' construction is a simple syntax error: you're missing the
'end select'; and in this case (as in most select case constructions) you
also need to deal with the possibility that the case will be other than
green.

 

[Dan Kelly]

Sorry, my mistake.

There are more options and an End Select in the actual code - I'd dropped
the former for brevity, and the latter by mistake

So I still need an answer to why declaring cb as Private causes the Select
Case to fall over...

 

[Jezebel]

Decalring cb as private doesn't cause your select case to fall over.
Something else is going on; not apparent from what you've posted so far.

 

[Greg Maxey]

Confirming Jezebel's comment. Things work just fine on this in using
the code snipets that you have provided.

I your complete code a state secret? It would be easier for me to
participate if I saw the complete picture.

 

[Jean-Guy Marcil]

Dan Kelly was telling us:
Dan Kelly nous racontait que :

I am trying to get my head around Private declarations and
AutoNew/AutoOpen options.

I have the following code that works:

Option Explicit
Dim cb As CommandBarComboBox

Sub AutoNew()
SetComboBox
End Sub

Private Sub SetComboBox
Set cb = CommandBars("Test").Controls.Add(msoControlComboBox)
with cb
.OnAction = "SetColour"
.AddItem "Green"
' Other setup stuff that works....
End With
End Sub

Private Sub SetColour
Select Case cb.Text
Case "Green"
'Run another Macro that doesn't matter here
EndSub

If however I declare AutoNew as Private so that it is hidden from the
Macro list then the above breaks at the Select Case Statement.

If your goal is to hide the AutoNew sub form the list, you should use
ThisDocument.Document_New instead.
AutoNew and AutoOpen are deprecated.

Also, a global variable is not needed here:
For example, the following is much more reliable.
Also, your code, as is, needs to run every time the document is opened or
created for the global variable cb to equal something other than "Nothing."

Sub AutoNew()
SetComboBox
End Sub

Private Sub SetComboBox
Dim cb As CommandBarComboBox
Set cb = CommandBars("Test").Controls.Add(msoControlComboBox)
with cb
.Caption = "ColourList"
.OnAction = "SetColour"
.AddItem "Green"
' Other setup stuff that works....
End With
End Sub

Private Sub SetColour
Select Case CommandBars("Test").Controls.("ColourList").Text
Case "Green"
'Run another Macro that doesn't matter here
EndSub

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org