Select Case 2

[WaterSprite]

The following code works:

Dim aa7 as string, aa8 as string

aa7 = "Maintenance Notified:"
aa8 = Inputbox(Maintenance Notified, Enter Yes, or No")
if aa8 = "yes" then cells(8,2) = aa7 & chr(32) & aa8

This code does not work:

Dim aa7 as string, aa8 as string

aa7 = "Maintenance Notified:"
aa8 = Inputbox(Maintenance Notified, Enter Yes, or No")

select case aa8
case is = "yes"
cells(8,2) = aa7 & chr(32) & aa8
end select

In the version using select case, the value in the variable aa8 does not seem to be "Seen" and the cursor goes straight to end select. I do not understand how the value in aa8 can be seen one time and not in the other.

 

[mscallisto]

try something like this:

aa8 = "yes"
Select Case aa8
Case "yes"
MsgBox ("yes")
Case "no"
MsgBox "no"
End Select

 

[mscallisto]

sorry I was typing and chewing gum at the same time.

aa8 = Inputbox(Maintenance Notified, Enter Yes, or No")

Select Case aa8
Case "yes"
MsgBox ("yes")
Case "no"
MsgBox ("no")
End Select

 

[firefytr]

You're not checking the case of it. So "Yes" does not equal "yes", and "yes" does not equal "yes " (notice the space). You're asking for trouble if you're going to rely on textual input from users, however, and I'd just recommend a message box, trapping the results. Here is what I'd do:

Dim aa7 As String, aa8 As VbMsgBoxResult
aa7 = "Maintenance Notified?"
aa8 = MsgBox(aa7, vbYesNo, "Choose Yes, or No")

Select Case aa8
Case vbYes
Cells(8, 2) = aa7 & ": yes"
Case vbNo
'user chose no
End Select

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[firefytr]

To add, if you're checking text, make sure you look at the Trim() and Ucase()/Lcase() functions. One way to make that work might be...

Select Case Trim(Ucase(aa8))
Case "yes"

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[HughLerwill]

Select Case Trim$(Ucase$(aa8))
Case "YES"

 

[Andrzejek]

Thia works fine for me:

Dim aa7 As String, aa8 As String

aa7 = "Maintenance Notified:"
aa8 = InputBox("Maintenance Notified, Enter Yes, or No")

Select Case Trim(UCase(aa8))
    Case "YES"
        MsgBox "Yes."
End Select

Have fun.

---- Andy

 

[fumei]

While it CAN work to get text input from users, I side with Zack. Avoid a text "yes" or "no". It is simply prone to error. And ticking off users. If they type "yess", or "yes " - the space Zack mentions - it will NOT be "yes". The users may insist they DID type it correctly, and who are you to say otherwise?

Say they typed "yess", all the UCase and Trim in the world will still have it come out as an error.

If you are requiring, essentially, a boolean answer...give them a boolean choice. Not a textual one. A message box with a Yes and No button makes it unequivocal. A boolean answer is returned. Although of course it is really True/False.

But at least it avoids having to use Trim, UCase yadda yadda yadda...with the possibility it still could be in error.


unknown

 

[firefytr]

Yup, and that's why I almost always go userform - without text boxes! (Except for a simple yes/no message box.)

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[fumei]

True, and why I also go userform when a userform is best. The right tool for the right job. There is a (small) place for InputBox, or the result of MsgBox, but if it is more than three pieces of user data I want/need...a userform is best IMO. And, designed and executed well, the zone of possible errors is greatly reduced.

And yes, I also try and keep the use of textboxes to a minimum. Depending of the situation, 99% of the time you can get the user information by other means.




OK, it is not 99%. I made that up. A majority of the time? 50% + 1???

For example, I fought like crazy to get someone to accept a combobox with a list of possible offices. They wanted the user to type in the name of the office. I wanted to make a list of possible offices, and have the user select one. Why is simple. There is ZERO possible of a input error. An incorrect one, sure (the user selects the wrong office). But there NO possibility of them putting "adgkbcsowq". Or something ruder.

Which I have seen. I have seen a document go out from a Director that had the eff word, because they had weak/lazy (read stupid) error trapping.

Error trapping is hard enough. ANYTHING that reduces the chance of user error is a good thing.


unknown

 

[DaveInIowa]

I agree with the above posters that a user form or, in your particular case, a message box would be the way to go. If you still want to go with a simple input box or have more than a possible Yes/No value, here's some code that extends the InputBox so the user is forced to enter a correct value or to cancel:

Public Sub Test()
    Dim result As String
    result = RestrictedInputBox("One, Two, Three", "Is it One, Two or Three?", "Dumb Question", "Four")
    Select Case result
        Case "": MsgBox "User canceled"
        Case "One", "Two", "Three": MsgBox "User entered: " & result
        Case Else: MsgBox "I'm pretty sure this can't happen!"
    End Select
End Sub

Public Function RestrictedInputBox(choices As String, prompt As String, Optional title As String, Optional default As String) As String
    Dim decoratedPrompt As String
    Dim validChoice As Boolean
    Dim vChoice As Variant
    
    RestrictedInputBox = default
    
    decoratedPrompt = vbCrLf & vbCrLf & prompt
    
    Do While (decoratedPrompt > "")
        RestrictedInputBox = Trim(InputBox(decoratedPrompt, title, RestrictedInputBox))
        
        decoratedPrompt = ""
        If (RestrictedInputBox > "") Then
        
            validChoice = False
            For Each vChoice In Split(choices, ",")
                If (LCase(RestrictedInputBox) = LCase(Trim(vChoice))) Then
                    RestrictedInputBox = Trim(vChoice)
                    validChoice = True
                End If
            Next
            
            If (Not validChoice) Then
                decoratedPrompt = "The value '" & RestrictedInputBox & "' is not a valid choice" & vbCrLf & vbCrLf & prompt
            End If
            
        End If
        
    Loop
    
End Function

 

[fumei]

Interesting.


unknown

 

[firefytr]

To say the least. Don't think it'd be in my top 10 of choices, but an option.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[fumei]

Well it certainly is a way to get a specific set of strings though. Although, again, a userform with a combox with only those strings would do the same job.


unknown

 

[Markus4]

Hi all,

may I jump in, even though I'm neither a programmer nor really have much experience?

I do prefer the InputBox, as it seems to be faster than a user-form. To me more important is another thought: even though one is using a computer one often has to repeat procedures (sorry, I can not find a better way to express myself). Therefore I use an InputBox with a lengthy explanation text, and code numbers for the results. To me this seems a more practical approach compared to a userform. User does not have to click on anything. The InputBox appears, user types a number, hits enter, and that was it.

Below is an example of what I'm doing. It's full of observations of practice but contains no programming "delicatessen". To be honest: I'm quite proud of it and that's why I'd like to show it.

Generally my macro below does the following:

- there are several different quotation marks
- in German texts quotation marks are always in pairs (unlike in English texts where quotation marks are repeated at the beginning of every paragraph of the quote)

So my macro
- enters a pair of quotation marks (and moves the cursor in between) or
- puts the Selection in quotation marks (and moves the cursor behind the quote).

As the user will most likely like to have the same quotation marks in a certain document, his/her choice is stored in a document variable. The next time my macro is called it will insert the same type of quotation marks without asking (but of course the user can delete the document variable).

I use this approach for brackets and dashes, but without remembering the type.

Just have a look.

Private Sub QuotationMarks(Optional simple As Boolean, _
  Optional clear As Boolean) 'MH 09.09.05
'Meine Version des Anführungszeichen-Einfügens: _
fragt nach der Art der Anf.zeichen; speichert diese Auswahl im Dokument selbst (damit der Benutzer bei jedem Doc einmal seinen Stil wählen kann und nicht jedes Mal gefragt wird); fügt zwei Anführungszeichen ein oder umschließt die Markierung. Kann die gespeicherte Auswahl auch wieder löschen, wobei ich bewußt auf eine Rückfrage verzichte.
On Error Resume Next
'0. Fehlerquellen ausschließen
  If Documents.Count = 0 Then 'schneller Ausstieg, keine Datei offen
    Application.StatusBar = "  Wäre ein Dokument aktiv, " & _
      IIf(clear, "würde jetzt die gespeicherte Auswahl gelöscht", _
      "würden jetzt " & IIf(simple, "einfache ", "doppelte ") & _
      "Anführungszeichen eingefügt.")
    Exit Sub
  End If
  If ActiveWindow.View.Type = 4 Then Exit Sub '4=wdPrintPreview. _
  Zwar läuft das Makro in der Seitenansicht, aber man sieht nicht, was passiert, deshalb raus.
  With ActiveDocument
'0. Speicherung eventuell löschen (bei DokVar-Namen auf 1.a) achten) und raus
    If clear = True Then
      .Variables("QuotationMarksDouble").Delete
      .Variables("QuotationMarksSimple").Delete
      Application.StatusBar = "  Gespeicherte Anführungszeichen-Auswahl gelöscht"
      Exit Sub
    End If
'1. Vorarbeiten: Variablen, DokVarName, Meldungstitel definieren, Array füllen
Dim quots()   'Array mit 1) Anführungszeichen, 2) Erläuterungstext
Dim varName$  'Name der DokVar mit der Art der Anführungszeichen
Dim msg$, t$  'Meldungstext und Ergebnis, Meldungstitel
Dim vbTab$    'Folge von Leerzeichen für Abstand in Meldungstexten
  vbTab = Space(5)
Dim i%        'Schleifenzähler und Auswahlergebnis
    'a) Variablenname und Meldungstitel
    'Da doppelte Gänsefüßchen weitaus häufiger sind als einfache, _
    belege ich doppelte vor und ändere nur bei simple = true ab.
    varName = "QuotationMarksDouble"
    t = "Doppelte"
    If simple = True Then
      varName = "QuotationMarksSimple"
      t = "Einfache"
    End If
    t = "  " & t & " Anführungszeichen einfügen"
    'b) Array füllen
    'Der Erläuterungstext wird mit Apostrophen gemacht, weil die MsgBox typographische Anführungszeichen als schwarze Striche darstellt. Sonst hätte ich alle Erläuterungen so wie quots(4,1) gemacht.
    If simple = False Then
      ReDim quots(1 To 5, 1 To 2)
      quots(1, 1) = Chr(187) & "abc" & Chr(171): quots(1, 2) = quots(1, 1) & vbTab & "französische (Guillemets)"
      quots(2, 1) = Chr(132) & Chr(147): quots(2, 2) = ",,abc´´" & vbTab & "typographische, oben und unten"
      quots(3, 1) = Chr(148) & Chr(147): quots(3, 2) = "``abc´´" & vbTab & "typographische, beide oben"
      quots(4, 1) = Chr(34) & "abc" & Chr(34): quots(4, 2) = quots(4, 1) & vbTab & "normale (Hochkommata)"
      quots(5, 1) = Chr(171) & "abc" & Chr(187): quots(5, 2) = quots(5, 1) & vbTab & "Guillemets für fremdsprachl. Texte"
    Else
      ReDim quots(1 To 2, 1 To 2)
      quots(1, 1) = Chr(146) & Chr(145): quots(1, 2) = "`abc´" & vbTab & "typographische, beide oben"
      quots(2, 1) = Chr(39) & "abc" & Chr(39): quots(2, 2) = quots(2, 1) & vbTab & "normale"
    End If
  '2. Was will der User?
    'a) Abfrage, wenn bisher noch keine Wahl getroffen
    msg = .Variables(varName).Value 'Speicherung auslesen
    If Err.Number = 5825 Then 'keine Wahl gespeichert
      msg = vbNullString
    'a) Art der Anführungszeichen abfragen
      For i = LBound(quots()) To UBound(quots())
        msg = msg & CStr(i) & vbTab & quots(i, 2) & vbCr
      Next i
      msg = Left(msg, Len(msg) - 1) 'letztes vbCr aus der Schleife weg
      msg = "Bitte die Art auswählen. Es gibt:" & vbCr & msg & vbCr & _
        "Bitte gewünschte Nummer eingeben. " & vbCr & vbCr & _
        "- Dann wird das gewählte Paar Anführungszeichen eingefügt oder die Markierung damit umschlossen." & vbCr & _
        "- Ihre Wahl wird für jedes einzelne Dokument gespeichert." & vbCr & _
        "- Beim nächsten Mal kommen die gleichen Anführungszeichen ohne vorherige Abfrage." & vbCr & _
        "- Nach " & Chr(187) & "Speicherung löschen" & Chr(171) & " (im Menü Einfügen - Anführungszeichen) können Sie wieder neu wählen. "
      i = CLng(Fix(InputBox(msg, t, 2)))
    'b) Abfrageergebnis prüfen, bei Fehler aussteigen
      If i < LBound(quots()) Or i > UBound(quots()) Then
        msg = "Falsche Eingabe, deshalb abgebrochen."
        If i = UBound(quots()) + 1 Then msg = "Abbruch durch Benutzer." ' _
        unter W98, WW97 ist i beim Abbruch der InputBox immer 1 höher _
        als die Obergrenze des Array. Geht aber nur mit OnErrorResumeNext _
        zusammen. Merkwürdig, aber praktisch.
        MsgBox msg, , t
        Exit Sub
      End If
      'Auswahl speichern
      .Variables.Add varName, CStr(i) 'DokVar-Inhalt ist jetzt geprüft
    Else
      i = CLng(msg) 'Benutzerauswahl/DokVar-Inhalt auslesen
    End If
    On Error GoTo 0 'OnErrorResumeNext aufheben
  End With 'ActiveDocument
'3. Abfrage umsetzen
  msg = quots(i, 1) 'evtl. schneller - und i wird frei
  With Selection
    Select Case .Type
      Case 1 'Cursor ist Strich
        .InsertAfter Left(msg, 1) & Right(msg, 1) 'Probetext weg
        .Collapse 0 'wdCollapseEnd
        .MoveLeft 1, 1, 0 'um 1 Zeichen, nicht erweitern
      Case 2 'Cursor markiert Text
        If Right(.Range, 1) = " " Then .MoveEnd 1, -1 '1=wdCharacter. Wenn _
        rechtes Ende der Markierung Leerzeichen, dies entmarkieren (wdMove=-1)
        .InsertBefore Left(msg, 1)
        .InsertAfter Right(msg, 1)
        .Collapse 0 'wdCollapseEnd
      Case Else
        MsgBox "Diese Art der Markierung (Selection.Type " & CStr(.Type) & ") " & _
        vbCr & "kann Word nicht in Anführungszeichen setzen.", , t
        Exit Sub
    End Select
  End With 'Selection
  Erase quots
End Sub   'QuotationMarks

Markus

 

[Andrzejek]

User does not have to click on anything. The InputBox appears, user types a number, hits enter, and that was it.

In your UserForm as input box, you have a label, text box and a command button with Default property set to True so "user types a number [or anything else], hits enter, and that [is] it" What's so different?

So my macro
- enters a pair of quotation marks (and moves the cursor in between) or
- puts the Selection in quotation marks (and moves the cursor behind the quote).

Easy:
1.

Private Sub UserForm_Activate()

With TextBox1
    .Text = Chr(34) & Chr(34)
    .SelStart = 1
End With

End Sub

2.

Private Sub UserForm_Activate()

With TextBox1
    .Text = Chr(34) & "Whatever you want " & Chr(34)
    .SelStart = Len(.Text)
End With

End Sub

A little less code than above....

Have fun.

---- Andy