Hide combobox entry options please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Evening,

I wish to hide two entries in a combobox.
Please can you advise what options are available.

The two entries in question must be in the combobox BUT not shown.
Deleting these two entries isnt an option
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
What is the case of adding entries if they cannot be displayed, then they simply should not be added.
It is not possible to load entries and hide some entries.
 
Upvote 0
DanteAmour i agree with your reply.

It was going to be a workaround for a problem i have.

Here is the code below.

When the combobox has only 1 entry left then i see an error message.
When i debug this is shown in yellow

NameForDateEntryBox.List = .Range("L1:L" & cntr - 1).Value

So i thought if i added 2 entries BUT hide them then i would see the message


Code:
Private Sub UserForm_Initialize()Dim cl As Range
Dim rng As Range
Dim lstrw As Long
Dim lastrow As Long
Dim Lastrowa As Long
Dim cntr As Integer
'==============================================================================================
Application.ScreenUpdating = False
lastrow = Sheets("POSTAGE").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("POSTAGE").Cells(8, 2).Resize(lastrow - 7).Copy Sheets("POSTAGE").Cells(1, 12)
Lastrowa = Sheets("POSTAGE").Cells(Rows.Count, "L").End(xlUp).Row
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Sort key1:=Cells(1, 12).Resize(Lastrowa), order1:=xlAscending, Header:=xlNo
CustomerSearchBox.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Clear
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
cntr = 1
With Sheets("POSTAGE")
    lstrw = .Range("B65536").End(xlUp).Row
    Set rng = .Range("B8:B" & lstrw)
        For Each cl In rng
            If cl.Offset(0, 5).Value = "" Then Sheets("POSTAGE").Range("L" & cntr).Value = cl.Value: cntr = cntr + 1
        Next
     .Range("L1:L" & cntr - 1).Sort key1:=.Range("L1"), order1:=xlAscending, Header:=xlNo
     
      NameForDateEntryBox.List = .Range("L1:L" & cntr - 1).Value
     .Range("L1:L" & cntr - 1).Clear
     TextBox2.SetFocus
End With
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Application.ScreenUpdating = True


TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
TextBox7.Value = Format(CDbl(Date), "dd/mm/yyyy")
End Sub
 
Upvote 0
You asked a similar question a few moments ago about a Msgbox when list=2. Did that answer not help you.
 
Upvote 0
MAIT

I believe you were the member who organised this sort code in the first place.

Worksheet name POSTAGE
Customers name are in column B
On the customers row in column G will either be empty or a date.

The customers names are only collected in the cell at column G is empty.
These names are then sorted in column L
After they have been sorted they are loaded into the combobox A-Z

My problem is when there is 1 name left i get an error.

It should be possible to have any amount of names without an error message BUT should there be 0 names then show msgbox saying No names present etc.
 
Upvote 0
The code you showed is not my code.

Ok i thought it was but nevermind.

Are you able to assist with this please.

If there is nothing to sort in column L etc then show msgbox when you try to open anthing apart from this error message
 
Upvote 0
Try this:

Code:
Private Sub UserForm_Initialize()
  Dim cl As Range, rng As Range, lstrw As Long, lastrow As Long, Lastrowa As Long, cntr As Integer
  '==============================================================================================
  Application.ScreenUpdating = False
  lastrow = Sheets("POSTAGE").Cells(Rows.Count, "B").End(xlUp).Row
  Sheets("POSTAGE").Cells(8, 2).Resize(lastrow - 7).Copy Sheets("POSTAGE").Cells(1, 12)
  Lastrowa = Sheets("POSTAGE").Cells(Rows.Count, "L").End(xlUp).Row
  Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Sort key1:=Cells(1, 12).Resize(Lastrowa), order1:=xlAscending, Header:=xlNo
  CustomerSearchBox.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value
  Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Clear
  '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  cntr = 1
  With Sheets("POSTAGE")
    lstrw = .Range("B65536").End(xlUp).Row
    Set rng = .Range("B8:B" & lstrw)
    For Each cl In rng
      If cl.Offset(0, 5).Value = "" Then Sheets("POSTAGE").Range("L" & cntr).Value = cl.Value: cntr = cntr + 1
    Next
[COLOR=#0000ff]    If cntr = 1 Then[/COLOR]
      [COLOR=#ff0000]MsgBox "No data[/COLOR]"
[COLOR=#0000ff]    ElseIf cntr = 2 Then[/COLOR]
      NameForDateEntryBox.[COLOR=#ff0000]AddItem .Range("L1").Value[/COLOR]
[COLOR=#0000ff]    Else[/COLOR]
      .Range("L1:L" & cntr - 1).Sort key1:=.Range("L1"), order1:=xlAscending, Header:=xlNo
      NameForDateEntryBox.List = .Range("L1:L" & cntr - 1).Value
      .Range("L1:L" & cntr - 1).Clear
      TextBox2.SetFocus
    End If
  End With
  '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  Application.ScreenUpdating = True
  TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
  TextBox7.Value = Format(CDbl(Date), "dd/mm/yyyy")
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top