Combo Box

  • Thread starter Thread starter Yvonne Grant
  • Start date Start date
Y

Yvonne Grant

I want to use combo boxes in a report but do not want the
drop down arrow to be seen when I print the sheet. Is this
possible?

Thanks, Yvonne
 
Hi Yvonne

Here's a variation of the metric code at
http://www.erlandsendata.no/english/index.php?d=envbawssetrowcolumnmm

Sub SetWidth()
Dim W As Double, C As Long
C = ActiveCell.Column
On Error Resume Next
W = CDbl(InputBox("Width for column number " & _
C & ":", "Enter inches:"))
If W > 0 Then Call SetColumnWidthInches(C, W)
End Sub

Sub SetColumnWidthInches(ColNo As Long, inchW As Double)
Dim W As Double
If ColNo < 1 Or ColNo > 255 Then Exit Sub
Application.ScreenUpdating = False
W = Application.InchesToPoints(inchW)
While Columns(ColNo + 1).Left - Columns(ColNo).Left - 0.1 > W
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth - 0.1
Wend
While Columns(ColNo + 1).Left - Columns(ColNo).Left + 0.1 < W
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth + 0.1
Wend
Application.ScreenUpdating = True
End Sub
 
If you right click on the combobox, there's an option for Format Control, then
Properties. Uncheck the "print object" box.

then if the combobox is from the controltoolbox toolbar, you could set the
linked cell to be right under the combobox.

If the dropdown is from the Forms toolbar, you could use a formula using the
linked cell and the input range for the dropdown.

I used A1:A10 for my inputrange and B1 for my linked cell:
=IF(B1="","",INDEX(A1:A10,B1))

Then I could still see the values, but not the combo/dropdown on the hardcopy.
 
Back
Top