Condition is False, MsgBox executed anyway?

Allround_it_er

New Member
Joined
Aug 5, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Below some code to search if the entered number through a combobox is higher than a number in a column.
The code works fine for everything I want to do, except for the last condition:

VBA Code:
ElseIf cboZoekProduct > LastCell Then
    MsgBox "In de afdeling 'Traiteur' is het laatste artikelnummer " & LastCell & "." & vbNewLine & _
    "Het getal mag dus niet groter zijn dan " & LastCell & "!!", vbCritical, "Getal groter dan " & LastCell & "!"
End If

CboZoekProduct = 1012 and LastCell = 5045
although 1012 is not > then 5045
the msgbox appears.
How is that possible?

Below complete event:

VBA Code:
'*****************************************************************************
' Is the number higher than the highest number starting with 1, 2, 3, 4 or 5 *
'*****************************************************************************
Dim rij1 As Long
Dim rij2 As Long
Dim rij3 As Long
Dim rij4 As Long

Dim found1 As Range
Dim found2 As Range
Dim found3 As Range
Dim found4 As Range

Dim CellValue1 As String
Dim CellValue2 As String
Dim CellValue3 As String
Dim CellValue4 As String
Dim LastCellA As Range
Dim LastCell As Range


Set found1 = Sheets("Producten_nieuw").Columns("A").Find(What:="2***", LookIn:=xlValues, lookat:=xlWhole)
    rij1 = found1.Row - 1
    CellValue1 = Range("A" & rij1).Value
Set found2 = Sheets("Producten_nieuw").Columns("A").Find(What:="3***", LookIn:=xlValues, lookat:=xlWhole)
    rij2 = found2.Row - 1
    CellValue2 = Range("A" & rij2).Value
Set found3 = Sheets("Producten_nieuw").Columns("A").Find(What:="4***", LookIn:=xlValues, lookat:=xlWhole)
    rij3 = found3.Row - 1
    CellValue3 = Range("A" & rij3).Value
Set found4 = Sheets("Producten_nieuw").Columns("A").Find(What:="5***", LookIn:=xlValues, lookat:=xlWhole)
    rij4 = found4.Row - 1
    CellValue4 = Range("A" & rij4).Value
Set LastCell = Sheets("Producten_nieuw").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    LastCell.Select
Set LastCell = LastCell.Offset(0, -2)

'Set LastCellA = Range("A" & Rows.Count).End(xlUp) 'second way to determine value of last cell in column "A"
    
If cboZoekProduct > CellValue1 And cboZoekProduct < 2000 Then
    MsgBox "In de afdeling 'Brood' is het laatste artikelnummer " & CellValue1 & "." & vbNewLine & _
    "Het getal mag dus niet groter zijn dan " & CellValue1 & "!!", vbCritical, "Getal is groter dan " & CellValue1 & "!"
ElseIf cboZoekProduct > CellValue2 And cboZoekProduct < 3000 Then
    MsgBox "In de afdeling 'Diepvries' is het laatste artikelnummer " & CellValue2 & "." & vbNewLine & _
    "Het getal mag dus niet groter zijn dan " & CellValue2 & "!!", vbCritical, "Getal is groter dan " & CellValue2 & "!"
ElseIf cboZoekProduct > CellValue3 And cboZoekProduct < 4000 Then
    MsgBox "In de afdeling 'Boterkoeken' is het laatste artikelnummer " & CellValue3 & "." & vbNewLine & _
    "Het getal mag dus niet groter zijn dan " & CellValue3 & "!!", vbCritical, "Getal is groter dan " & CellValue3 & "!"
ElseIf cboZoekProduct > CellValue4 And cboZoekProduct < 5000 Then
    MsgBox "In de afdeling 'Patisserie' is het laatste artikelnummer " & CellValue4 & "." & vbNewLine & _
    "Het getal mag dus niet groter zijn dan " & CellValue4 & "!!", vbCritical, "Getal is groter dan " & CellValue4 & "!"
'ElseIf cboZoekProduct > LastCell And cboZoekProduct < 5999 Then
ElseIf cboZoekProduct > LastCell Then
    MsgBox "In de afdeling 'Traiteur' is het laatste artikelnummer " & LastCell & "." & vbNewLine & _
    "Het getal mag dus niet groter zijn dan " & LastCell & "!!", vbCritical, "Getal groter dan " & LastCell & "!"
End If

Help is very much appreciated. Thx
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Does
VBA Code:
ElseIf CLng(cboZoekProduct) > LastCell Then
make a difference?
 
Upvote 0
Solution
The problem is that if cboZXOekProduct is a combobox, the value is going to be text. Text will always be greater than a number. As @MARK858 shows you must convert the text to a number.
 
Upvote 0
The problem is that if cboZXOekProduct is a combobox, the value is going to be text. Text will always be greater than a number. As @MARK858 shows you must convert the text to a number.
Thanks for the explanation!
I will change the other conditions to.
 
Upvote 0

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top