Not case sensitive vba script

Question

I need to make script not be case sensitive when executing if clause.. I've placed the "compare.mode=vbTextcompare not working " on different location in the script in order to solve this problem but is not working in my case... There is no error or any other problem with script.. The comparison is between a range of name all starting with capital letter but the matching part is with lower case... any solutions for this?

Sub DropDown14_Change()
Dim ScCell As Range, key
Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
Set ws = Worksheets("Dashboard")
Set wsD = Worksheets("DATA")
Set complistDict = CreateObject("Scripting.Dictionary")
Dim DD14V As Variant



complistDict.RemoveAll
Dim DD14 As Object
Set complistDict = Nothing
Set complistDict = CreateObject("scripting.dictionary")

ws.Shapes("Drop Down 16").ControlFormat.RemoveAllItems

Set DD14 = ws.Shapes("Drop Down 14").OLEFormat.Object
Set DD16 = ws.Shapes("Drop Down 16").OLEFormat.Object
DD14V = DD14.List(DD14.Value)

'ws.dropdown14.Clear
 For Each ScCell In wsD.Range("E2", wsD.Cells(Rows.Count, "E").End(xlUp))
 Dic.CompareMode = vbTextCompare
If ScCell.Value = DD14V Then
If Not Dic.Exists(LCase(rCell.Offset(, -1).Value)) Then
Dic.Add LCase(rCell.Offset(, -1).Value), Nothing

End If

End If
Next ScCell
'MsgBox DD14.List(DD14.ListIndex)

 For Each key In Dic
 DD16.AddItem key
 Next



 End Sub

What do you mean by "not working". Please give specific example. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 21, 2019 at 12:17

Ron Rosenfeld · Accepted Answer · 2019-07-21 12:35:29Z

1

If I understand you correctly, you want to make

If ScCell.Value = DD14V Then

be case insensitive.

You can either place

Option Compare Text

at the beginning of your macro, before the first Sub statement.

Or, to limit the case insensitivity to just that line, you can use:

If Ucase(ScCell.Value) = Ucase(DD14V) Then

But your code seems to have other errors.

I would suggest you place Option Explicit at the beginning of your macro (before the first Sub) which will help with debugging.

I don't understand why MS makes NOT requiring variable declaration the default. Select Tools/Options/Editor and check Require Variable Declaration. This will place Option Explicit at the start of any new module.

It may also help to use Early binding instead of Late binding for your dictionary object to get the benefit of Intellisense.

edited Jul 21, 2019 at 12:35

answered Jul 21, 2019 at 12:05

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martinswe Over a year ago

Thats correct, I would like that part to be insensitve.. Will try

jmwfr · Accepted Answer · 2019-07-21 12:44:21Z

1

According to this

Try to use

Option Compare Text

On top of you code.

Hope it helps!

answered Jul 21, 2019 at 12:44

jmwfr

811 silver badge2 bronze badges

Collectives™ on Stack Overflow

Not case sensitive vba script

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related