remove item from combobox

sukyb1

Board Regular
Joined
Mar 27, 2009
Messages
153
I have userform with a combobox that fills up with data when the userform is opened. One of the enteries in this combobox is "Test"
I want to hide/delete this from the combobox.
How can I do this?

Thanks for any help in advance
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

sukyb1

Board Regular
Joined
Mar 27, 2009
Messages
153
AssetDataCells = "A" & cFirstDataRow & ":A" & lastDataRow
dataSheet.Range(AssetDataCells).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=dataSheet.Range(AssetDataCells), CopyToRange:=.Range("A1"), Unique:=True


cboAsset.List = Sheet2.Range("A2", "A10").Value


there is a filter on a sheet this gets all the unique records and fills the column A on sheet2.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,523

ADVERTISEMENT

There is a filter .... so why not filter out the unwanted value?
 

sukyb1

Board Regular
Joined
Mar 27, 2009
Messages
153
i have a useform, in order for that user form to open up I need one row of data this is the test row data. In Userform intialize it tells it to filter out data on a sheet which then provides the values for the combobox.
I need to have the test row in the background but dont want it to show up in the combobox.
 

sukyb1

Board Regular
Joined
Mar 27, 2009
Messages
153

ADVERTISEMENT

or is there a wat to hide the value from the combobox?
 

sukyb1

Board Regular
Joined
Mar 27, 2009
Messages
153
This is the code below. I have a command button that opens the userform. If i have no data in datasheet the userform doesnt open and throws back an error. but if i have a test row then the userform opens.


Private Sub UserForm_Initialize()

Dim LDate As String
Dim lastRowColA As Long
Dim AssetDataCells As String
Set tempsheet3 = Sheets(cTempSheet3Name)


LDate = Date
lblUsername = GetXLUserName
cboAsset.List = Sheet2.Range("A2", "A10").Value
cboAssetSelect.List = tempsheet3.Range("A1", "A10").Value
lblDateAuto = LDate
'Copy data to temporary sheet where it will provide a list of unique Assets for the cboAssey combobox and
'filtered data for the lstAssetEstimates listbox

Set dataSheet = Sheets(cDataSheetName)
Set temporarySheet = Sheets(cTemporarySheetName)

firstFilteredCopyRow = 0

'Determine the last row containing data

With dataSheet
lastDataRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

With temporarySheet

'Clear temporary data

.AutoFilterMode = False
.Cells.ClearContents

'Filter unique Assets on data sheet column B and copy list to temporary sheet column A

AssetDataCells = "A" & cFirstDataRow & ":A" & lastDataRow
dataSheet.Range(AssetDataCells).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=dataSheet.Range(AssetDataCells), CopyToRange:=.Range("A1"), Unique:=True

'Sort column A so that Assets are in alphabetical order

.Columns("A:A").Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes, MatchCase:=False

'Get the number of unique Assets and populate the cboAsset combobox. The combobox's RowSource
'starts at A2 so that it doesn't include the column header

lastRowColA = .Cells(.Rows.Count, "A").End(xlUp).Row
cboAsset.RowSource = temporarySheet.Name & "!" & .Range("A2:A" & lastRowColA).Address

'Copy data sheet columns A:F to temporary sheet columns B:G where it will be filtered to provide values for
'the lstAssetEstimates listbox

dataSheet.Range("A" & cFirstDataRow & ":F" & lastDataRow).Copy Destination:=.Range("B1")

End With

End Sub
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,523
{snip}If i have no data in datasheet the userform doesnt open and throws back an error. but if i have a test row then the userform opens.{snip}
This seems odd to me. What error is thrown back at you?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,601
Messages
5,597,117
Members
414,126
Latest member
jellevansoelen

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
Top