-
Jun 24th, 2019, 01:45 AM
#1
Thread Starter
Member
[RESOLVED] Default Excel User form Using VBA : Help on Unique Values.
Hi Friends,
I am stuck with one thing in a User form . I have a dropdown where the data is taking from a sheet. I want only unique values in the droplist. I already have the code , but can someone please help me to edit this code which will give only unique values in the droplist.
Note : Dropdown Button Name is cmbSelChartAttr and datas for this dropdown is taking from "Inconsistency Order" Sheet.
[CODE]:
Function formInitialize(ws As Worksheet)
Set ws = ThisWorkbook.Worksheets("Inconsistency Order")
If ws.Visible = True Then
ws.Activate
Else
ws.Visible = xlSheetVisible
ws.Activate
End If
Dim i, lc
Dim j
Dim tecAttrVal As String
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
i = 0
For i = i + 1 To lc
tecAttrVal = Cells(1, i).Value
Me.cmbSelChartAttr.AddItem tecAttrVal
'Me.cmbAttrXAxis.AddItem tecAttrVal
tecAttrVal = ""
Me.Label3.Visible = False
Me.lsbOrderSet4SelAttr.Visible = False
Next i
End Function
Thanks in Advance
Last edited by sachinns; Jun 24th, 2019 at 05:05 AM.
-
Jun 24th, 2019, 05:03 AM
#2
Re: Default Excel User form Using VBA : Help on Unique Values.
possibly the simplest method is to add the values to a collection, collections can not have duplicate values, then only add the value that can be added to the collection to be also added to the combobox
an alternative is to use sql to generate a recordset of unique values
you do not need the worksheet to be visible or activated for the code to work in most cases
Last edited by westconn1; Jun 24th, 2019 at 05:07 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 24th, 2019, 05:08 AM
#3
Thread Starter
Member
Re: Default Excel User form Using VBA : Help on Unique Values.
Thanks.
I am very new to VBA, so can you please modify the existing code . In which it will give only unique values in the combobox called "cmbSelChartAttr" .
-
Jun 24th, 2019, 05:48 AM
#4
Re: Default Excel User form Using VBA : Help on Unique Values.
I am very new to VBA, so can you please modify the existing code
you always will be if you do not have a go for yourself
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 24th, 2019, 06:07 AM
#5
Re: Default Excel User form Using VBA : Help on Unique Values.
try modifying like
Code:
Dim c As Collection
Set c = New Collection
On Error Resume Next
For i = i + 1 To lc
tecAttrVal = Cells(1, i).Value
c.Add tecAttrVal , tecAttrVal
If Err.Number = 0 Then
cmbSelChartAttr.AddItem tecAttrVal
Else
Err.Clear
End If
Next i
Me.Label3.Visible = False
Me.lsbOrderSet4SelAttr.Visible = False
i have only put in the additional and modified parts
Last edited by westconn1; Jun 24th, 2019 at 06:46 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 24th, 2019, 06:13 AM
#6
Thread Starter
Member
Re: Default Excel User form Using VBA : Help on Unique Values.
Thanks a lot. Its working for me...
Client is asking for one weird condition. Ex: if in ‘Inconsistency order’ sheet there are two columns called “ABC” and “ABC – Check” in the dropdown will be reported only “ABC” .
Is it possible to do this on VBA ?
-
Jun 24th, 2019, 06:45 AM
#7
Re: [RESOLVED] Default Excel User form Using VBA : Help on Unique Values.
Is it possible to do this on VBA ?
probably, but i do not understand what you want
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 24th, 2019, 07:27 AM
#8
Thread Starter
Member
Re: [RESOLVED] Default Excel User form Using VBA : Help on Unique Values.
Hi,
I will explain it.
Suppose we have sheet with some column values. These values are listed in a combobox list. Example : We have values like : ABC , DEF , GHI , ABC - check , DEF , GHI - check .
So In our combobox droplist , It should only have ABC , DEF , GHI .
Basically if there are two columns called “Release 2” and “Release 2 – Check” , The droplist will only show “Release 2”
The code for this combobox is same as above.
-
Jun 24th, 2019, 07:37 AM
#9
Re: [RESOLVED] Default Excel User form Using VBA : Help on Unique Values.
set a range of the 2 columns then add to the combobox using a collection as before
Code:
lr = usedrange.rows.count
set r = range("a1:b" & lr)
for each cel in r
you would have to specify the worksheet for the usedrange
Last edited by westconn1; Jun 24th, 2019 at 07:42 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 24th, 2019, 07:51 AM
#10
Thread Starter
Member
Re: [RESOLVED] Default Excel User form Using VBA : Help on Unique Values.
Where to add this code ?? My sheet name is "Inconsistency Order" and combobox name is "cmbSelChartAttr" .
This is the code for my combobox list.
Function formInitialize(ws As Worksheet)
Set ws = ThisWorkbook.Worksheets("Inconsistency Order")
If ws.Visible = True Then
ws.Activate
Else
ws.Visible = xlSheetVisible
ws.Activate
End If
Dim i, lc
Dim j
Dim cell As Range
Dim tecAttrVal As String
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
i = 0
Dim c As Collection
Set c = New Collection
On Error Resume Next
For i = i + 1 To lc
tecAttrVal = Cells(1, i).Value
c.Add tecAttrVal, tecAttrVal
If Err.Number = 0 Then
cmbSelChartAttr.AddItem tecAttrVal
Else
Err.Clear
End If
Next i
Me.Label3.Visible = False
Me.lsbOrderSet4SelAttr.Visible = False
End Function
Last edited by sachinns; Jun 24th, 2019 at 08:03 AM.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|