VBA If Else or Select Case

[Michaels]

Hi All,

My code below is working fine with If Else block but I am trying hard to use select case in this code instead of if with calculation/editing inside the select code block:

Sub ChangePOQty()
Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim FindString As String
    Dim Rng As Range


    Set ws = ThisWorkbook.Worksheets("Sheet1")
    FindString = UserForm1.TextBox1.Value
                
    If Not IsNumeric(UserForm1.TextBox3.Value) Or UserForm1.TextBox3.Value < 1 Or UserForm1.TextBox3.Value = "" Then
        MsgBox "Please double click the item to modify and enter positive quantity received.", vbInformation, "Quantity Received"
        With UserForm1.TextBox3
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End With


    ElseIf Trim(FindString) <> "" Then
        Set Rng = ws.Cells.Find(What:=FindString, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(0, 2)
        Rng.Value = UserForm1.TextBox3.Value
        With UserForm1
            .TextBox1.Visible = False
            .TextBox2.Visible = False
            .TextBox3.Visible = False
            .TextBox4.Visible = False
            .TextBox5.Visible = False
            .CommandButton1.Visible = False
        End With
    End If
Application.ScreenUpdating = True
End Sub

In other words, the below line of code has multiple conditions:

    If Not IsNumeric(UserForm1.TextBox3.Value) Or UserForm1.TextBox3.Value < 1 Or UserForm1.TextBox3.Value = "" Then

Then the ElseIf has this line:

    ElseIf Trim(FindString) <> "" Then

How can I use Select Case in above lines ?

Thank you for your help.

 

[tlowry]

My approach would be to use a function that returns a Boolean (True, False):

Function MeetsCriteria(val) As Boolean
    MeetsCriteria = True
    If Not IsNumeric(val) Then Exit Function
    If val < 1 Then Exit Function
    If val = "" Then Exit Function
    MeetsCriteria = False
End Function

Then call it in a Select Case:

Sub testit()
    Select Case MeetsCriteria(userform1.TextBox3.Value)
        Case True
            MsgBox "True"
        Case Else
            MsgBox "Not so much"
    End Select
End Sub

 

[Michaels]

Hi Tlowry,

Thank you for your code.

You see, when we use select case, apart from showing MsgBox in first if part, we would be performing actions too in ElseIf.

How can we use actions performing part and use that in Select Case ?