Hide combobox entry options please

[ipbr21054]

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

 

[DanteAmor]

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.

 

[My Aswer Is This]

Show us how your loading these values into your ComboBox

 

[ipbr21054]

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

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

 

[My Aswer Is This]

You asked a similar question a few moments ago about a Msgbox when list=2. Did that answer not help you.

 

[ipbr21054]

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.

 

[ipbr21054]

You asked a similar question a few moments ago about a Msgbox when list=2. Did that answer not help you.

I am not sure if that was for this joib or another now.

 

[My Aswer Is This]

The code you showed is not my code.

 

[ipbr21054]

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

 

[DanteAmor]

Try this:

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