Hide combobox entry options please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,182
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Show us how your loading these values into your ComboBox
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
You asked a similar question a few moments ago about a Msgbox when list=2. Did that answer not help you.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
The code you showed is not my code.
 
Last edited:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,182
Office Version
2007
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,345
Messages
5,486,323
Members
407,539
Latest member
ltwkuav

This Week's Hot Topics

Top