Need help unerstanding 'Select Case'

[BIRD_FAT]

I saw a question on another forum that asks how to use a combobox to insert addresses based on the user choice.
As I want to learn more about VBA, I thought I'd give it a go - yet, I have to admit defeat!
I have created the Listbox and populated it - added a call to 'PasteSequence', but I am having problems understanding Select Case!
I'm trying to get the paste sequence to set the various address lines by case, then insert the correct lines into a paste sequence at the end of the code.

What I would like to ask you guys is to have a look at my (obviously failed) code and send me in the right direction by explaining what I've done wrong.


Sub PasteSequence()
' Am I right to declare these as Strings?
Dim strLine1 As String
Dim strLine2 As String
Dim strLine3 As String
Dim strLine4 As String
Dim strPostcode As String
Select Case Range.AddAddress.Value
Case ComboBox1.Value = Company1
' The following line is where the code errors out 'strLine1 =' with
' Compile error: Object required'
Set strLine1 = "C1 Company Name"
Set strLine2 = "C1 Address Line 1"
Set strLine3 = "C1 Address Line 2"
Set strLine4 = "C1 Address Line 3"
Set strPostcode = "C1 Postcode"
Case ComboBox1.Value = Company2
**as above**
Case ComboBox1.Value = Company3
**as above**
Case ComboBox1.Value = Company4
**as above**
End Select
Selection.HomeKey Unit:=wdStory
Selection.ParagraphFormat.Alignment = wdAlignParagraphRight
Selection.TypeText Text:=strLine1
Selection.TypeParagraph
Selection.TypeText Text:=strLine2
Selection.TypeParagraph
Selection.TypeText Text:=strLine3
Selection.TypeParagraph
Selection.TypeText Text:=strLine4
Selection.TypeParagraph
Selection.TypeText Text:=strPostcode
Selection.MoveDown Unit:=wdLine, Count:=1
End Sub


So if anyone has time to explain how to correctly use the Select Case function, I would be most appreciative!

[Paul_Hossler]

Try something like



Case Company1
' your code if Range.AddAddress.Value = 'Company1'
Case Company2
' your code if Range.AddAddress.Value = 'Company2'
Case Company3
'your code if Range.AddAddress.Value = 'Company3'


in all places


Select Case will use the Select value (Range.AddAddress.Value) test each of the Case values (Company1, 2, 3 ...) and execute the Case code

You can use a Case Else to catch all others


Paul

[BIRD_FAT]

That doesn't seem to work either! Nor do If/ElseIf statements.

Both Error out at 'Sub or Function not defined':banghead:

So, just to ensure clarity - I have:


Removed the Dim statements from the beginning
Changed each of the cases to be the full paste sequence with correct values
Now, what should my Select Case be - my call is to the PasteSequence code - could this be a misnaming issue?:help

[Paul_Hossler]

Sorry, I really only looked at the Select Case code

Select Case is sort of like a Switch that takes the value of the Case Select and finds the corresponding Case value



Select Case ComboBox1.Value
' if ComboBox1.Value = Company1 then execute the Case
Case Company1
' Only use 'Set with objects, otherwise just assign with =
strLine1 = "C1 Company Name"
strLine2 = "C1 Address Line 1"
strLine3 = "C1 Address Line 2"
strLine4 = "C1 Address Line 3"
strPostcode = "C1 Postcode"
Case Company2
' **as above**
Case Company3
' **as above**
Case Company4
' **as above**
End Select


The rest I left alone, since without a sample doc, I couldn't tell if what you told the computer to do was what you wanted the computer to do



Paul

[BIRD_FAT]

The rest I left alone, since without a sample doc, I couldn't tell if what you told the computer to do was what you wanted the computer to do
Paul
Not enough posts to upload docs yet, so I'll tr google docs (no idea if it will allow you to download and retain the vba, but if not, let me know, and I will post each of the code pieces that I have, and list where they are kept.

Here's the link to the GoogleDoc (https://docs.google.com/Doc?id=dhbt9z9k_1fjq8j4c4&hl=en) - let me know if it works!

What I am trying to achieve is for a combobox to popup when the document is opened (will be saved as a template); the combobox (or listbox) will have a list of company names in it. When the user chooses one of the names, I am looking for the correct address to be inserted into the document.

I can do all the formatting, etc. but I was thinking that using Select Case would be the way to go to get the choice from the combobox translated into a paste sequence using the correct version of the address!

Thanks for the last bit of code by the way - It shows the Case Select better, hopefully I'll get the understanding of this soon and can add it to my VBA database for future use!

[lucas]

I didn't have permissions to access your file posted on google but maybe the attachment will help you understand the select case.

You could use a bookmark to put the info into a specific place in the doc instead of at the selection if you need to.

[lucas]

without the command button....just make the selection. Bookmark added to make it easier to locate the address on the document.

You can just save these as template files, ie .dot after changing the company names and addy's and you are in business.

You could also use an array but I didn't want to make it too complicated. Advise if that interests you.

[BIRD_FAT]

Thanks a lot Paul and Lucas, I'll post any q's I have (if that's OK by you!).



You could also use an array but I didn't want to make it too complicated. Advise if that interests you.

ALWAYS looking to learn - the more I can get, the more I can learn, so - 'Yes please!':cloud9:

[fumei]

Hopefully this has been resolved. I am checking back through older threads, and would like to comment.

1. Select Case Range.AddAddress.Value

This is invalid syntax. VBA will have no idea what Select Case Range.AddAddress.Value refers to!

2. Keep your Dim declarations, but as Paul mentioned, you use Set with objects, not string variables.

3. Steve (lucas) gives a decent example, but I would avoid using Selection, that is, selecting the bookmark and then typing text. You can insert whatever you want into a bookmark range directly.

[lucas]

Steve (lucas) gives a decent example, but I would avoid using Selection, that is, selecting the bookmark and then typing text. You can insert whatever you want into a bookmark range directly.


Updated version attached. Selection removed. Thanks Gerry.

[lucas]

Array version if you are interested. See attached

[mdmackillop]

I've don't see it specifically mentioned, but you need to use a string or numeric in Select Case.

So


Select Case ComboBox1.Value
Case "Company1"
'etc.

[Paul_Hossler]

To expand that point a bit, you can use Enum's and built in wd-constants, since they are basically numeric




Option Explicit

Enum eSample
eTop = 1
eMiddle = 2
eBottom = 3
End Enum

Sub Sub1()
Dim L As Long
Dim E As eSample
E = eMiddle
Select Case E
Case eTop
MsgBox "Top"
Case eMiddle
MsgBox "Middle"
Case eBottom
MsgBox "Bottom"
Case Else
MsgBox "None of the Above"
End Select
L = 2
Select Case L
Case wdLeft
MsgBox "Left"
Case wdRight
MsgBox "Right"
Case Else
MsgBox "None of the Above"
End Select
End Sub


Paul

[fumei]

"I've don't see it specifically mentioned, but you need to use a string or numeric in Select Case."

Malcolm...hmmmm, while technically correct, this may give an incorrect impression. Select Case can use anything that can be validated, or in technical terms, has a value. This includes Boolean (True/False) values. True, true...in real terms, booleans are, in fact, behind the scenes, numeric. We do not see that though.

For example:

Sub MD()
Select Case ActiveDocument.FormFields("Text1").CalculateOnExit
Case True
MsgBox "Yes Calculate on exit is checked." & vbTab & _
ActiveDocument.FormFields("Text1").CalculateOnExit
Case False
MsgBox "No, Calculate on exit is NOT checked." & vbTab & _
ActiveDocument.FormFields("Text1").CalculateOnExit
End Select
End Sub

will display either - depending on whether Calculate on exit is checked, or not:

Yes Calculate on exit is checked. True

OR

No, Calculate on exit is NOT checked. False

So the code uses Select Case on a Boolean value quite nicely.

Any property value can be used with Select Case, because ALL properties - when it comes down to the guts of the matter - are, as you correctly state, either strings or numeric.

Here is another example.

Sub MD_2()
Dim strOther As String
Select Case ActiveDocument.FormFields(1).Type
Case wdFieldFormTextInput ' using constant as a string
MsgBox "The first formfield in this document is a " & "TEXT formfield."
Case Else
Select Case ActiveDocument.FormFields(1).Type
Case 71 ' the numeric value of wdFieldFormCheckBox
strOther = " CheckBox "
Case 83 ' the numeric value of wdFieldFormDropDown
strOther = " DropDown "
End Select
MsgBox "The first formfield in this document is a " & _
strOther & "formfield."
End Select
End Sub


Notice that Select Case is being used on the numeric index item of the formfields collection - Formfields(1). My point being is that it is too easy to forget that Word VBA really uses numbers mostly, and that essentially ALL object properties are numeric, and can be used with Select Case.