Setting VBA Combobox Case Sensitivity

Jun 5, 2014

I am working on a simple ActiveX combobox with "Y/N" options. I am trying to allow for the user to simply type either an upper or lowercase "y" or "n" without it having such stringent case sensitivity. I've looked all over for solutions and tried several (Option text compare statement, Target = UCase(Target) placed at various points in my code) and I still am getting the same "The value you entered is not valid" error.

I don't want to use a standard dropdown because the text looks miniscule without zooming (which I rather not have the user need to do). I rather not have a Y/y/N/n list, as it looks amateurish. How to stop the case sensitivity?

View 14 Replies


Defeating Case Sensitivity In VBA Compare

Apr 4, 2014

I am doing a compare (<>) of two strings in a VBA IF statement. See the code below ...4th line has the IF statement. I want the compare to be case INSENSITIVE. It's not. "Thomas" = "thomas" results in FALSE.


I tried changing the if statement to read:

[Code] .......

With this, VBA complained that the function was not supported. For fun, I tried LOWER and got the same result. Typically I punt in this situation and store the mixed case text in an active, associated worksheet and have an on-sheet formula do the "upper", then pickup the upped text from the cell with the upper formula. In this case this approach is not particularly convenient.

View 3 Replies View Related

Join Cell Text & Compare With Case Sensitivity

Jul 20, 2007

Looking for formula with if then statement that involves a nested formula or concatenation. I need a formula that says: If cell A reads exactly ABCD then concatenate cell A with cell C. Very basic to most of you gurus but I'm fairly new with formulas.

View 3 Replies View Related

Setting TextBox Font (Select Case)

Aug 13, 2009

I m trying to Setting of TextBox font (Select Case)

MY attempted

View 6 Replies View Related

Test If Combobox List Contains A Value Before Setting Value

Apr 22, 2014

I'm trying to programmatically set the value of a combobox but I'm running into errors if the combobox list does not already contain the value I'm trying to set it to. How can I first test if the combobox has that as a valid option and, if not, add that item?

View 2 Replies View Related

Setting A Cell Equal To The Selected Value In A Combobox

Mar 26, 2007

I know you can set cells equal to other cells, but can you set the cell equal to a combobox created in VBA.

View 2 Replies View Related

Variable Appended To Combobox Name In Select Case

Apr 30, 2007

I have a userform with 3 Comboboxes, all of which need to be populated from the dropdown option before OK is pressed. I haven't used any fancy names as this is just a straightforward (or so I thought) entry form ... the UserForm is called UserForm1 and the 3 ComboBoxes are called ComboBox1, ComboBox2 & ComboBox3. I have tried to be economical with my validation routine but I cannot get an incremental variable added to the end of the word "ComboBox" to be recognised ... my code is below :

Private Sub Validate_ComboBoxes()
While MyComboCount < 3
MyComboCount = MyComboCount + 1
MyComboBox = "ComboBox" & MyComboCount
Select Case "UserForm1." & MyComboBox <<<< This fails
Select Case UserForm1.ComboBox1 <<<< This works
Case ""
MyValidCheck = MyValidCheck + 1
Select Case MyComboCount
Case "1"
MsgBox "Enter the number of weeks for this period"
Case "2"
MsgBox "You need to enter a start date"
Case "3"
MsgBox "You need to enter an end date"
End Select
End Select
If MyValidCheck > 0 Then
MyComboCount = 4
End If
End Sub

View 3 Replies View Related

Sensitivity Table

May 16, 2007

I need help building a sensitivity table using my attached model.

What would first quarter 2007 EPS be ( CELL D52) if total Selling, General and Administrative (cell D22 is the dollar amount with the increase showing in cell D65) was anywhere from 12 to 15%?

View 8 Replies View Related

Sensitivity Table For Given Return In Derivatives Model

Oct 15, 2007

I've inherited the following code, which uses solver to create a sensitivity table for a given return in a derivatives model:

Private Sub CommandButton1_Click()
'Yield Analysis
'Application. ScreenUpdating = False
x = Range("Correlation")
v = Range("SwapCur")
w = Range("DebtLTV")
Range("ValYLD").Value = 0.005
For i = 1 To 5
For j = 1 To 7
Range("ValYLD").Value = 0.005
Range("DebtLTV") = 0
a = Range("IRRung")
b = Sheets("Cashflow").Cells(35 + j, 4)
c = Range("SwapFX")
d = Sheets("Cashflow").Cells(35, 4 + i).................

It seems that the code does not cope well when negative numbers are used as inputs, as the results it gives are unexpected and incorrect. I have fiddled with various elements but to no avail. All inputs are percentages. i= 1to 5 are always positive, j 1-7 percentages, centred around "SwapFX". It is these inputs, along with "swapcur" which are now negative, and previously have not been.

View 5 Replies View Related

Sensitivity Analysis Returning A Multiple On Percentage Data

Aug 2, 2008

I want to set up an automatic sensitivity analysis such as the one shown in example.doc attached, using my model that is attached (inventory model. zip). Going horizontally in the sensitivity analysis, I want to show 2H (3Q and 4Q) Sales Growth (30%, 35, 40,45,50,55,60%) and vertically I want to show 2Q Inventory Growth (50%, 55%, 60%, 65%, 70%, 75%, 80%) and I want the inside data to return sales/inventory spread ( multiple). Basically it would divide the dollar growth in sales in 2H by the dollar growth in inventory and return a multiple. Is it possible to set this up automatically using the data that I have here?

View 2 Replies View Related

Excel 2010 :: Sensitivity Analyses - Dispersion Diagram / Chart

Dec 12, 2012

How to create a dispersion diagram which will show me the impact of 3 variables on oil-in-place, which for the sake of this description, I shall call 'X'.

The 3 variables (let's call them A, B and C as I needn't go into the details) are used in the calculation for X. I have created tornado diagrams, which show me the positive and negative impact of each variable. But I'd like to create a diagram with X along the x-axis, with X increasing, and on the y-axis, have -1 to +1 which represents the impact of the variables A, B and C on X. So, as X increases, variable A might also increase toward +1, which would show me that as variable A increases, so does X. The reason why I'd like to learn how to do this is, whilst a tornado diagram will show me the impact of each variable on X, a dispersion diagram will also show be trends between variables. So, for example, variables B and C might both increase along the same trend, which would show me two things: that they both positively impact X and that one variable is dependent on the other.

I remember from a few years ago that an old version of Excel had a data sensitivity tool which did something like this. I'm working off of Excel 2010 now, and I've had a little look at the 'whatif' functions, but that doesn't seem to be what I'm looking for. Also, I would kind of like to see how to do this myself with the raw data, instead of the behind-the-scenes stuff.

View 2 Replies View Related

Add Characters Between Lower Case And Upper Case Letters

Aug 26, 2009

I have a string of names that run together without spaces or commas between each name.

"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.

Is there a way to add a comma and space between a lower case and upper case letter?

View 7 Replies View Related

Proper Case/Sentence Case In Macro Code

May 8, 2008

Sub Addy()
Do Until ActiveCell. Offset(0, -4) = ""
Renamer = Proper(ActiveCell)
ActiveCell = Renamer
ActiveCell.Offset(1, 0).Select
End Sub

fail? Trying to remove all capitals from names/addresses. Error message is "compile error - sub or function not defined"

View 6 Replies View Related

Select Case, Case Else Copy From Above Cell

Jun 3, 2009

I've got a pretty intense macro already written, a lot of Select Case components. At the end, if nothing matches I'd like to just copy the cell above to the cell below. However, there is a range of about 400 cells in length, so I'd need some sort of wildcard for range.

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Dim Cell As Variant
For Each Cell In Range("A1:OL1")
Select Case Cell.Value
Case "Eng1"
Cell.Offset(1, 0).Value = "Engine One"
tons more in the middle here
Case Else
Cell.Offset(1, 0).Value = "N/A"

Rather then returning "N/A", how could I reference the cell above and just copy it instead?

View 9 Replies View Related

Case Statment Not Stopping At A Case

Apr 22, 2009

I decided to try to change it into a Case Statement. Here is what I have now. But the problem seems to be this time at this line: When I have "01" in C5 the script just keeps going?

View 14 Replies View Related

Lower Case To Upper Case

Jun 20, 2008

When I use a simple formula such as:


that will obviously change whatever is in a1 to Upper Case - but it will put it in the cell that holds the formula.

What I want to know is:
Is there any way I can format the cell to run the formula when the information has been pasted into the spreadsheet

View 9 Replies View Related

Select Case Error "Case Without Select Case"

Jul 20, 2006

For the following code, I'm getting the " Case without Select Case" error (On Case 3 to 5...assuming more are wrong too, but debug can't get there yet). I thought I had it right, obviously don't. Can anyone spot how my code is wrong? ....

View 9 Replies View Related

Changing CASE To Case

May 15, 2007

I'm trying to compare two range values within a macro to see if they match...if they do/don't I capture and write some other data.

One list is lowercase, while the other list is UPPERCASE.
My current macro needs them to be in the same case because I'm using the following to compare:
If VPNID.Value = BuildHRName Then

How can I change the format of one of the lists to UPPERCASE or lowercase so that I am comparing apples to apples?

View 9 Replies View Related

Upper Case To Lower Case And Lower Case To Upper Case?

Nov 17, 2009

if there's any way for vba to detect if each individual character in a string is in caps, and if so, convert to lower case, and if it's in lower case, convert to capitalized

The text will vary in length and content... so he wants to see how we can change:
"This Is Strange" to "tHIS iS sTRANGE"
"THIS IS STRANGE" to "this is strange"
"this is strange" to "THIS IS STRANGE"

View 2 Replies View Related

Populating Combobox 2 With Items That Match Criteria From Combobox 1

Mar 30, 2009

Am trying to get dynamic population of 2nd combobox based on match from criteria in combobox 1.

if column a = bears and column b = colours of bears then

when I select bears in combobox one, combobox 2 would populate with colors of bear.

I am think of having a combobox 1 change event that evaluates each row in a specific range (does it match the criteria?) if so, then add 2nd cell (column b) of that row to the combobox 2.

I know it would probably involve match and offset, add item and loop, but I am not sure what the syntax is.

View 9 Replies View Related

Auto Populate 2nd Combobox Based On 1st Combobox Data

Mar 30, 2014

I need to populate two combo boxes from excel sheet, the data will be like below:

Column A Column B
A 1
A 2
A 3
A 4
A 5
B 100
B 101
B 102

So from the above data, one combo box should hold unique values A & B.

On selecting a value from the 1st combo box A or B, respective values should be populated in 2nd combo box.

So the data should be like below:

If A is selected in the 1st combo box, then 2nd combo box should only show the values 1,2,3,4 & 5.
If B is selected in the 1st combo box, then 2nd combo box should only show the values 100,101 & 102.

Friends I need it in a macro and one important point is, this is dynamic and it is not static and the data can be more.

View 1 Replies View Related

Dismiss ComboBox Dropdown While ComboBox Code Is Still Running

Dec 8, 2009

When I make a comboBox selection from the dropdown menu, the menu stays down until the last statement of the comboBox code is finished. How do I make the dropdown disappear immediately after the selection is made?

View 9 Replies View Related

Filling Combobox Based On Another Combobox.text Value.

Jun 20, 2009

try and achive is when a user selects a item from the 1st Combobox the 2nd Combobox is the populated with the cell that is to the right of the selected item.

for Example if a user selects AAB from combobox1,, Combobox2 should populate with Belly.

Maby using combobox1_Exit for the excercise.

View 14 Replies View Related

ComboBox Selection That Fills A TextBox And Other ComboBox

Sep 28, 2013

I created a UserForm then linked ComboBox1 to range A2:A, TextBox2 to range E2:E, and ComboBox3 to range M2:M of the same worksheet, named Sheet3. The row contents in Column A, Column E, and Column M are associated. Therefore, when the UserForm is active I want to be able to select a row from Column A in ComboBox1 and have the UserForm pull the contents from the same row of Column E into TextBox2, and Column M into ComboBox3. Here is what I have so far, but its not quite doing it.

Private Sub UserForm_Initialize()
Dim ColARange As Range


View 2 Replies View Related

Populate Combobox Based On Selection Of Other Combobox

Jun 12, 2006

I have a table, headers "FirstName" and "SurName".

Further a Userform with 2 Comboboxes "FirstName" and "SurName"

I'd like to choose the FirstName (say Jack) in the "FirstName" combobox, and based on that get the choice of the Surnames of all my Jacks in the "SurName" combobox.

Actually my sheet has much more fields and comboboxes, but i think my problem is just that I do not find a way to populate them dynamically.

View 9 Replies View Related

Set ComboBox Value Based On Text If In The ComboBox List

Feb 8, 2008

How can i Loop through a combobox's values and compare to a string value and then set the listindex of the combobox to that value?

View 5 Replies View Related

Set ComboBox RowSource To Result Value Of Another ComboBox

Nov 27, 2012

I have created a userform in which there are numerous Text and Combo boxes. In ComboBox 1 I have, in effect, a list of named ranges in a spreadsheet.

I want the RowSource for ComboBox 2 to equal the item selected (named range) in ComboBox 1.

Private Sub UserFormcriteria_Click()
Set ComboBox2.RowSource = ComboBox1.Value
End Sub

View 8 Replies View Related

Filling Two ComboBox Depending On Another ComboBox And ...

Jan 23, 2009

I want to fill two Combobox (cmb 2 and 3) dependent on what the user has chosen in a first ComboBox (cmb1).

The first combobox ist based on on the first sheet, and the second and third combobox shall be filled with values from other worksheet corresponding on combobox 1 selection.

I attached a xls to this post to explain better.

View 14 Replies View Related

Userform Combobox Value Selects Next Combobox To Appear

Dec 2, 2009

I have a userform with a combobox that has three items to choose from: Blue, Red, and Yellow. When a user selects one of those options, I would like another combobox to appear on my userform with a specific list for that option.

View 2 Replies View Related

Userform - Second Combobox Based On First Combobox

Apr 9, 2012

I'm trying to make a userform that has 2 combo boxes. I have just 3 columns right now.

Procedure GrpADA-QSI DescProcedure GrpAnesthesia And
Drugs9210-LOCAL ANESTH/NO SURGAnesthesia And DrugsAnesthesia And
Drugs9212-TRIGEM BLOCK ANESTHCrowns And BridgeAnesthesia And
Drugs9215-LOCAL ANESTHESIADenturesAnesthesia And

[Code] ........

I copied and pasted Column A into Column C and then removed duplicates. I named Column C 'ValList' and placed it in the RowSource for ComboBox1. What I now want is for ComboBox2 to populate based on my selection in ComboBox1. There are no duplicates in Column B. Duplicates are in Column A.

I also named Column A 'Proc_Grp' and Column B 'ADA_QSI_Desc'

For the properties in Combobox2, I left the RowSource empty. (that's correct right?) Because there's going to be a code that links Combobox2 to Combobox1... I think...

View 6 Replies View Related

Copyrights 2005-15, All rights reserved