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!
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!