Combobox default value script

[trevford1]

Hello, I could use some help with a Combobox script. I don't know much so please be thorough in any explanations. Here's the code and I'll try to explain the logic:

Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B6").Value <> 0 Then
Range("S4") = ComboBox1.Value 'save and remember this as the default value.
End If
If Range("M5").Value = "Corner Lot" Or Range("M5").Value = "Daylight Corner" Then
ComboBox1.Value = Range("S2")
End If
If Range("F6").Value <> 0 Then
ComboBox1.Value = Range("S5") 'resets to the original user combobox selection
Else
Range("S5") = Range("S4") 'this should really say something like Range("S5") = whatever the user may change the combobox to. Please make this the new default value
End If
End Sub

This code is a terrible cobble-job, but it almost works. I sure it must be confusing without the sheet to visually refer to.

The main trouble is that it resets to the original Combobox selection, but then it won't let the user make any changes (always resets to Range("S5") of course).

Here's the logic of the what I'd really like the code to do:
1. In the Combobox,
the user will select from 3 different options. There's a default option and if they make no other selection, then default is the choice.

2. By inputting a value in Range("B6"), excel should remember the Combobox selection and store in on the sheet somewhere. It should not change unless the user deliberately alters the combobox (though excel itself will change the value of Combobox1 as the user enters data)

3. As the user inputs data in different places on the sheet, it will change the Value in cell M5. For example, "Daylight Corner" or "Corner Lot". (I have other formulas on the sheet that detect this and change M5 from "Corner Lot", "Daylight Corner" and "Standard Lot").

4. If Range("M5") does not say "Daylight Corner" or "Corner Lot" then the Combobox should reset to the original selection of the user.

5. After that, the user should still have the option to change the Combobox, and if they do, the excel will remember that as the new default.

The way the program is working now, the user makes a selection in the Combobox, but once the data is entered, they can't change it anymore. They should be able to change it should they need to.

The last line resets the Combobox, but doesn't allow the user to make any change:

If Range("F6").Value <> 0 Then
ComboBox1.Value = Range("S5") 'resets to the original user's choice
' (insert code here) - The combobox should now be changable. If the user does change it, then excel will again remember this as the new default.

I'm sure this code could be scrapped and done a whole lot better and more effiecient and I certainly welcome that. I also find that scrolling down through the rows is a little slower with this code.

Even if somebody bangs out a decent idea, I may be able to figure something out, as long as I got a clear example to refer to.

Thanks for the help!

 

[trevford1]

No takers, eh?

How about this:

What's a simple line of code for the ComboBox to allow any selection after it resets to the default value?

 

[Sal Paradise]

It sounds like you want something more like this. I'm not quite sure though.

Please try explaining what you want more clearly.

 

[trevford1]

Thanks so much for the response. I've spent many hours trying to figure out this simple thing. I knew it might be confusing, which is why I tried to outline the purpose of what I was trying to do. Unfortunately, it's probably information overload. Plus my system is very inefficient, with different things happening that are difficult to understand unless you see it in action.

I looked through the link you gave me, but I couldn't see anything that would be relevant, unless I missed it. I'm thinking of a code fix rather than a cell formula or validation.

Ok, forget everything else I wrote. I'll try to keep it simple this time. Here's the code:

Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B6").Value <> 0 Then
Range("S4") = ComboBox1.Value
End If
If Range("M5").Value = "Corner Lot" Or Range("M5").Value = "Daylight Corner" Then
ComboBox1.Value = Range("S2")
End If
If Range("F6").Value <> 0 Then
ComboBox1.Value = Range("S5")
Else
Range("S5") = Range("S4")
End If
End Sub

Here's what you can help me with. Please note the last couple lines of code (from "Else" onward). I am going to add some notes to help you understand what I really want it to do:

Else
Reset the Range("S5") to = Range("S4"). S4 is the default ComboBox selection. (at this point, I can't change the ComboBox selection, since the only logical thing left for it to do is refer to Range("S4").

Once the Range is reset to "S4", don't keep changing the Combobox value to Range("S4") (which it is doing), but allow me to change the Combobox selection if I want to.

If I do change the Combobox value, then please remember that as the new default value.

End Sub

If you need more background information, please let me know and I'll be happy to try to explain. I appreciate your help. This is a program for work, so they would be grateful too. Thanks.

 

[trevford1]

Hmmm. Guess it's still confusing for everybody.

 

[Sal Paradise]

What confuses me is that the user doesn't seem to be able to pick from the Combobox at all since it seems like it's only a single choice. Am I missing something?

Basically, what I need to know is what you want the ComboBox to do, where it gets that data from, and how you want to change that list.

For instance, if you want the user to pick from a list in A1:A10 to start, and then change to B1:B10 if cell S4 = "Corner Lot" or whatnot, that's no problem. But I am totally unclear on how your Combobox is interacting with your sheet, and your code doesn't make it any clearer, nor does your explanation.

 

[trevford1]

Oh my, the overall interaction is very complicated and may confuse matters even more. I'll try my best to fill in the blanks. If you give up after seeing this post, I don't blame you.

(Refering to the code in my last post)

The spreadsheet is a calculator. The user will make a selection from the Combobox first (or use the default).

Data is always entered from left to right in cells B6, C6, E6 and F6. The Combobox value changes according to when data is entered in these cells as further explained:

The Combobox is ActiveX and refers to cell range S1 to S3. For the sake of simplicity, lets say the 3 items are S1:Apple, S2:Orange and S3:Banana.

Cell M5 has several formulas that recognizes when data is entered in cells B6, C6, E6 and F6. This cell value will change to "Daylight Corner" or "Corner Lot" accordingly, which will also change the calculation formulas.

The Combobox value will also change depending on cell M5 (ex; if the value is "Corner Lot" and "Daylight Corner" then the value will change to S2:Orange).

Cell S4 is where the default value is copied to. (Ex: apple) Unfortunately, as data is entered in the other cells, this will will also change with the Combobox value (Entering any data into cell E6 will change the value in cell M5 to "Corner Lot").

Before this can happen, we will copy cell S4 value (apple) to cell S5 (Apple). Cell S5 now becomes the new default reference cell (what a cobble-job, eh? Yeah I don't know much.) This takes place in the last line of code:

Else
Range("S5") = Range("S4")

If the user enters data into cell F6, cell M5 value changes back to the default (Cell S5 which will be "Apple").

Works ok so far. Unfortunately, the user cannot change the default value after this step. THIS is the big problem. The Combobox will only reset to "Apple".

I want the user to be able to change the Combobox to any of the original ranges in S1:S3 (ex: S3: Banana). And if they do, then S3: Banana becomes the new default. This of course excludes "Daylight Corner" and "Corner Lot", which must be value S2: Orange.

If you're still with me, thanks very much. You are very patient. If not, thanks for your efforts anyway. It's appreciated.