Solved: Data Validation -- Combo box VBA

[tools]

Hi all
I am new to VBA and I want to use Data Validation to create a drop down list of options in a cell using VBA.
Is there a way to do this.?

Thanks

[tstav]

Hi tools,
welcome to the forum.
Of course there is a way. You may start with the macro recorder and see what you get. Then you can customize the code (if it needs to) or ask back.

Regards

[tools]

Hi
I am familiar with macro recorder.
I have created the list by going to the Data tab and using validation.
what i am tryin to do is to create the listbox dynamically depending on some condition and the location keeps on changing .
I hope i am clear.
Is this possible ?

Thanks

[tstav]

I'll be away for about an hour, tools.
Give the forum members as much information as you can about what you want to do, so that they can help you faster.

e.g. what is this condition you mention, what location are you referring to etc.
I'll be back

Regards

[tools]

thanks tstav :)

I want to do something like the code written below
col refers to the column (value of col would not change)
num refers to the row location


Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Excel Data")
Dim rng As Range
Dim LBObj As OLEObject
Set rng = ws.Range((col) + (CStr(num + 1)) + ":" + (col) + (CStr(num + 1)))
Set LBObj = ws.OLEObjects.Add(classtype:="Forms.ComboBox.1", Link:=False, _
Top:=rng.Top, Left:=rng.Left, Height:=17, Width:=rng.Width)
LBObj.Name = "MY dynamic listbox"

For k = 0 To UBound(arrdefval) - 1
LBObj.Object.AddItem "" + arrdefval(k) + ""
Next


The problem with this code is that it allows the user to enter any value

Thanks

[tstav]

This is what I have understood:
You want the user to see a drop down list of items whenever they click on any cell of a specific column. This list contains the items of the arrdefval array.
Is that it?

If yes, then you don't need to dynamically create a combo box (unless you want to do it for educational reasons; just to get the hang of how it's done). You can do it by using Data Validation code.

If no, then more explaining would be in order.
Regards
Sub Test_Validation()
Dim k As Integer, arrdefval As Variant, str As String, col As Long
col = 3 '<-- supply the column number
arrdefval = Array("aaa", "bbb", "ccc", "ddd") '<-- supply your array
For k = 0 To UBound(arrdefval) - 1 '<-- why are you avoiding the last item?
str = str & arrdefval(k) & "," '<-- create comma delimited string of array's items
Next
str = Left(str, Len(str) - 1) '<-- eliminate trailing comma
With Columns(col).Validation '<-- create drop down list
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=str
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub

[tstav]

The creation of the string variable that contains the list items can also be done like this
'For k = 0 To UBound(arrdefval) - 1 '<-- why are you avoiding the last item?
' str = str & arrdefval(k) & "," '<-- create comma delimited string of array's items
'Next
'str = Left(str, Len(str) - 1) '<-- eliminate trailing comma
str = Join(arrdefval, ",") '<-- all items have been included here

[mdmackillop]

Hi Tools,
You're making life much more difficult by creating controls on the fly. Much better to use a userform or add them to the sheet and hide them. You can change properties by code where required, but all basic properties are saved with the control.

[david000]

never use the forms controls on the fly I agree with mdmackillop...

this has is draw backs but you can get the hang of it quickly. the key here is that you can FORMAT the cells any way you like and control the font size without a massive add-in.






Hi Tools,
You're making life much more difficult by creating controls on the fly. Much better to use a userform or add them to the sheet and hide them. You can change properties by code where required, but all basic properties are saved with the control.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim isect As Range

If Target.Cells.Count > 1 Then Exit Sub
Set isect = Application.Intersect(Range("ComboRng"), Target)

If Not isect Is Nothing Then

With ComboBox1
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height
.ListFillRange = Me.Range("M1:M" & Range("M" & Rows.Count).End(xlUp).Row).Address
.LinkedCell = Target.Address
End With

ElseIf ComboBox1.Visible Then ComboBox1.Visible = False

End If

End Sub

[tools]

Hi,

Thanks tstav
That is exactly what i needed. :)

Thanks for your help mdmackillop and david000 :)

Thanks and Regards

[tools]

Hi all

I am using SOAP messaging to call a webservice through vba.
I am getting the following error

Automation error(80010108)
Application defined or Object defined error

This is the code that i have written


Dim env
Set env = CreateObject("pocketSOAP.Envelope.2")
env.EncodingStyle = ""
env.SetMethod "getCoreTaskDescription", "link"

env.Parameters.Create "id", "2281"
env.Parameters.Create "applicationType", "Planning"
env.Parameters.Create "nodeType", "Projlet"

Dim http
Set http = CreateObject("pocketSOAP.HTTPTransport")
http.SOAPAction = ""
http.SetProxy "localhost", 9090
http.Send "link", env
Dim firstNode
' now, we need to parse the SOAP message we get as a response
env.Parse http
Dim i As Integer
Dim arr As Variant
ReDim arr(1)
Dim col
col = "Z"
i = 0

For Each params In env.Parameters
Set firstNode = env.Parameters.Item(i).Value
arr(i) = firstNode.Nodes.ItemByName("taskIdDescription").Value
i = i + 1
Next


Dim k As Integer, str As String

For k = 0 To UBound(arr)
str = str & arr(k) & ","
Next
str = Left(str, Len(str) - 1)

With Columns(col).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=str
.IgnoreBlank = True
.InCellDropdown = True
End With


It throws an error at the following line
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=str


Thanks

[tstav]

Hi tools,
try it like this: Set arr(i) = firstNode.Nodes.ItemByName("taskIdDescription").Value

[tools]

Hi tstav,

It gives me a type mismatch error when i use Set.

I wanted to ask one more question, the drop list that we are creating now is for a particular column. Now I wanted to do the data validation for a particular cell.

Thanks

[tstav]

tools, hi
I haven't replicated your code so I'm sorry if any of my suggestions just plain fail.

As far as the one cell validation, you may change the
With Columns(col).Validation, to
With Range("A3").Validation (A3 is just an example).

Now, about the error you mentioned in your post#11:
First: what is the error number and error description? Give us the full message, it might help.
Second: if you step through the code (pressing F8) and hover your mouse over the Ubound(arr) in the "For k=0 to Ubound(arr)" line, what does it say? What number does it give you?
Third: When you hover your mouse over the variable 'str', what does it say? Has 'str' gotten a string value, is it empty or what?

PS. When writing your answer posts, please remember to first press the VBA button and then insert your code between the two bracketted words ('VBA' and '/VBA'). It is much easier for everybody to read indented code, I know you can understand this.

Regards,
tstav

[tools]

Hi all

Error number : 1004.
Error description : Application defined or Object Defined Error.

When i hover my mouse over UBound(arr) it gives me the number 1

When i hover it over str it gives me "" that would be for the first iteration

Thanks

[tstav]

ok tools,
and what is the value of str AFTER the str = Left(str, Len(str) - 1) line has run? (hover your mouse over it again)

I'm starting to think that the problem lies with the For each params...Next loop. Are you sure arr(i) is being assigned any values at all?
I've never used those objects before so I wouldn't say more...

[tools]

Hi

After running the line str = Left(str, Len(str) - 1)
str contains open,closed,deferred
arr(i) is an array which conatins open,closed and deferred

The error occurs at the following line

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=strn

If the problem is with the for each params is there any other way to solve it.

Thanks

[tstav]

ok, last round now, because I think I'm running out of suggestions here...

Each iteration in the 'For each params...Next' loop assigns a value to a new item of the arr array.
1.In the code I can see Redim arr(1) which means arr can hold two items, arr(0) and arr(1).
2.You said that you can see that the Ubound(arr) is 1. This evidence agrees with the previous paragraph.
3.You say that str has the value "open,closed,deferred". Strange. How can it have three values when only two arr(i) items exist?
4.You say that arr(i) contains the values "open", "closed", "deferred". How can this be, when arr can hold only two items (remember? Ubound(arr)=1)

I'm out of questions, tools. It doesn't make sense. This is conflicting evidence.

Regards, tstav

[tools]

Sorry tstav

I was wrong in my last post the elements in the str array and that in str are perfect.

str contains open,closed,deferred
UBound(arr) gives me the value 2

I am tryin to create a standalone excel which could reproduce my error.
But when I hardcode the elements in arr the error vanishes
Here is the standalone code that i wrote and it works fine.
I have tried everythin possible .


Dim i As Integer
Dim arr
ReDim arr(1)
Dim col As String
col = "Z"
i = 0
For i = 0 To i = 2
arr(i) = "test" & i
i = i + 1
Next

Dim k As Integer, strn As String

For k = 0 To UBound(arr)
str = str & arr(k) & ","
Next
str = Left(str, Len(str) - 1)

With Columns(col).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=strn
.IgnoreBlank = True
.InCellDropdown = True
End With




Thanks and Regards

[tstav]

This loop is also wrong.
You can't use
"For i=1 to i=2", it has to be "For i=1 to 2", otherwise you will get only one iteration and the loop will be over.
On top of that you never increment i inside the loop (i=i+1) because this way you skip iterations.

But anyhow, if values are (as you say) being assigned correctly, then I see no reason for the code to fail.

I really hope somebody else will help you in some other way.
Regards
tstav

[tools]

Hi tstav

The loop was wrong :bow:

But when I change my loop to this it throws the same error


For Each params In env.Parameters
Set firstNode = env.Parameters.Item(i).Value
arr(i) = firstNode.Nodes.ItemByName("taskIdDescription").Value
i = i + 1
Next


Is there anything that can be done.

Thanks a lot for Your help tstav.

Regards

[mdmackillop]

1. Use Option Explicit
2. Don't use Functions (str) as variable names
3. For i = 1 to 2 (as tstave noted)
4. Don't increment within the loop (i=i+1). Try it with a message box to see the results
5. Add a debug messagebox to check array values as they are entered.
6. You have set your array size to 1 (2 elements) and are trying to add 3 (for 0 to 2)
7. Either set your array big enough to start with and redim it after all entries are inserted, or as shown, redim it as you go.
8. If you're gioing to use a sized array then dim it with brackets Dim Arr()
9. The items in a validation list need extra quotes when created in VBA. Record a macro creating a list "A","B","C" and you'll see what is required.
10. A minor point, but just use Long data type instead of Integer


Option Explicit
Sub Test()
Dim i As Long
Dim arr()
Dim Strn As String
Dim k As Long
Dim DQ As String
Dim col As String
col = "Z"
For i = 0 To 2
ReDim Preserve arr(i)
arr(i) = "test" & i
'MsgBox arr(i)
Next

DQ = Chr(34) & Chr(34)
Strn = DQ & "testing" & DQ & ","
For k = 0 To UBound(arr)
Strn = Strn & DQ & arr(k) & DQ & ","
Next
Strn = Left(Strn, Len(Strn) - 3) & DQ

With Columns(col).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Strn
End With
End Sub

[tools]

Hi

I am sending an excel sheet in which the error has been reproduced.
Click the save button to see the error.

Thanks & Regards.

[tools]

Hi all

Guyz guess what the error was THE SHEET WAS PROTECTED :banghead::banghead::banghead:

Thanks tstav :)
Thank you all for your help and support.

[tstav]

I can hear your sigh of relief clearly, tools...
And quit banging your head. Things like that have happened (and are still happening) to everybody, one way or another...

Have a nice day :)

[tools]

Hi all

I am having two data validation list in my sheet which were created as mentioned in this thread before.

Now I want to change the value of one list depending on the value of the another list.

For eg.

list1 has names of fruits list2 has color of these fruits
apple red
orange orange
banana yellow
watermelon green

If the user selects banana from list1 then the value in the next cell should be yellow and it should work the other way round also.

Is this possible..? How do I go ahead.

Thanks and Regards

[david000]

http://www.contextures.com/xlDataVal02.html


I guarantee that this will help you in very clear step-by-step instruction.


This Excel MVP specializes in anything done with the data menu e.g. data validation, pivot tables etc.

[tools]

Thanks david000

I want to do this using VBA. The data with me is goin to be dynamic so this wont help.

Regards

[tools]

To be more precise I am attaching an excel sheet.

The data in the validations list is obtained dynamically.
Locations of the lists would be available to me at run time.

Thanks and Regards

[Aussiebear]

Tools, would you mind starting another thread? Your origional thread topic is not related to this request.