Combo Box fine tuning

[FLHR01]

Hi,

It's been requested that I make the drop down list in our forms a larger font. Figured out that I have to do that through ActiveX Combo Box. I have my combo box set up, I just want to fine tune it.

I need to:

1. Print the information with out the drop down list arrow showing.
2. Conditional formating so that if it's blank it's yellow and if it's filled it's white
3. Tab out of the box into the next cell.

Thanks in advance.

FLHR01

[Whizbang]

1: Set the "PrintObject" property to False (this would also require that the "LinkedCell" property is set to some cell so that the value can be printed).
2: On the Home ribbon select "Conditional Formatting", then "Format only cells that contain", then select "Blanks" in the first box. Set the format by clicking "Format".
3: This would require VBA, and would be complex because there is no "OnTab" event on the combobox. You can tab to other controls, but not back and forth between the sheet and controls. The simple solution is to just use the "LinkedCell" property and let people who type the value in just tab through the cells, and people who need to use the dropdown to click the control. However, clickers would then also need to click to activate the next cell.

[FLHR01]

Thank you!

[FLHR01]

I have the combo box set up to print correctly but now I'm having problems melding the two together.

9-29-2014 4-03-58 PM.png

As you can see from the attachment, my combo box is over the cell I want it to populate. If I select the position, it shows up in both boxes. (The box behind is conditionally formatted to be yellow if blank or blue when filled)


What's the best course of action?

[FLHR01]

Here's the result9-29-2014 4-05-52 PM.png

[FLHR01]

Here are the properties. 9-29-2014 4-47-36 PM.png

[Whizbang]

After playing with this there are a few options

1: Set the "BackStyle" to transparent and then ensure that the text aligns exactly with the cell underneath.
2: Write some VBA code in the sheet that when that cell is selected the combobox becomes visible, but when the combobox is deactivated it becomes invisible
3: Leave the combobox as visible but change the text in the cell to white so it does not appear. This will require an OnPrint event on the worksheet to change the font color back to black on print, since the combobox will not print.

[FLHR01]

i found some code online but it didn't work.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboHomePosition As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler

If Target.Validation.Type = 3 Then
Cancel = True
End If

Set cboHomePosition = ws.OLEObjects("HomePosition")
On Error Resume Next
With cboHomePosition
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboHomePosition
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
.Object.Font.Name = "Century Gothic"
.Object.Font.Size = 16
End With
cboHomePosition.Activate
End If

TidyUp:
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox "Error " & Err & ": " & Error & vbNewLine & "in Worksheet_BeforeDoubleClick"
Resume TidyUp

End Sub

[Whizbang]

You have two instances where "cboHomePosition" should be "cboTemp". There was also an issue of when a user double-clicks on a cell that does not have Validation settings. You also want to set the visibility to False on LostFocus.

Please Login or Register  to view this content.

[FLHR01]

So I have multiple drop down items that I want to use. So I copied and pasted the previous VBA Code, replacing "HomePosition" with "Site".
Private Sub Site_LostFocus()
ActiveSheet.OLEObjects("Site").Visible = False
End Sub



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboSite As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler

If Target.Validation.Type = 3 Then
Cancel = True
End If

Set cboSite = ws.OLEObjects("Site")
On Error Resume Next
With cboSite
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboSite
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
.Object.Font.Name = "Century Gothic"
.Object.Font.Size = 16
End With
cboSite.Activate
End If

TidyUp:
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox "Error " & Err & ": " & Error & vbNewLine & "in Worksheet_BeforeDoubleClick"
Resume TidyUp

End Sub




Private Sub HomePosition_LostFocus()
ActiveSheet.OLEObjects("HomePosition").Visible = False
End Sub



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboHomePosition As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler

If Target.Validation.Type = 3 Then
Cancel = True
End If

Set cboHomePosition = ws.OLEObjects("HomePosition")
On Error Resume Next
With cboHomePosition
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboHomePosition
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
.Object.Font.Name = "Century Gothic"
.Object.Font.Size = 16
End With
cboHomePosition.Activate
End If

TidyUp:
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox "Error " & Err & ": " & Error & vbNewLine & "in Worksheet_BeforeDoubleClick"
Resume TidyUp

End Sub


I get the following error.9-30-2014 12-54-01 PM.png

[FLHR01]

By the way, THANK YOU SO MUCH for ALL your help!!! Couldn't have done it without you!!!

[Whizbang]

The code is already dynamic and you do not need to create copies of the code to accommodate other drop-downs.

What happens in this code is this:

User double-clicks a cell, any cell (intention of editing the cell)
The above script runs and checks if the cell has data validation set.
If data validation is set, it takes the hidden combobox, clears out anything in the LinkedCell, and ListFillRange properties that may have been set previously.
It then copies whatever data validation formula is in the selected cell and inserts that into the ListFillRange, and then also sets the LinkedCell to the selected cell address.

What this means is that you only need to create one combo box with the above code and it will work for EVERY cell with data validation.

I recommend clearing out your VBA module for the selected sheet and re-copying the code in my previous post.


Also, in the future, please wrap your code in CODE tags.

RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[FLHR01]

Thank you so much! VBA is still such a mystery to me. Working slowly to understand it.

I appreciate your patience and understanding and I will remember the CODE Tags next time.

[FLHR01]

One last question,

Is there any way to avoid the double click?

[Whizbang]

I am not sure what you mean. Do you mean have the combobox become visible when the cell is selected? I would recommend against that, because of the issue where you cannot tab out of the combobox.

[FLHR01]

Ok. Thank you!