Change a value into range

[dotchieJack]

Sorry great helpers
I have another problem (sorry if i make mistakes, my native language is dutch)
and i am very new in vba
I would like to change this

If ComboBox2.Value = "ADC" Then

Into something like this

If ComboBox2.Value = range("B1:B500") Then

Thanks

 

[Marc L]

Hi !

Could you explain what you are trying to achieve ?
​

 

[dotchieJack]

Hello,
thank you for your quick reponse,

Change this
Private Sub CommandButton1_Click()
Dim range As range
If ComboBox2.Value = "ADC" Then
UserForm2.Show
Else
If ComboBox2.Value = "Capacitors Ceramic" Then
UserForm3.Show
Else
If ComboBox2.Value = "Circular Military Connectors" Then
UserForm4.Show

End If
End If
End If
End Sub

Into

If ComboBox2.Value = range("B1:B500") Then 
UserForm2.Show
Else
If ComboBox2.Value = range("C1:C500") Then 
UserForm3.Show
Else
If ComboBox2.Value = range("D1:D500") Then 
UserForm4.Show
End If
End If
End If
End Sub

 

[dotchieJack]

Sorry,
change this

 Private Sub CommandButton1_Click()
If ComboBox2.Value = "ADC" Then
UserForm2.Show
Else
If ComboBox2.Value = "Capacitors Ceramic" Then
UserForm3.Show
Else
If ComboBox2.Value = "Circular Military Connectors" Then
UserForm4.Show

End If
End If
End If
End Sub

Into this

Private Sub CommandButton1_Click()
If ComboBox2.Value = range("B1:B500") Then
UserForm2.Show
Else
If ComboBox2.Value = range("C1:C500") Then
UserForm3.Show
Else
If ComboBox2.Value = range("D1:D500") Then
UserForm4.Show
End If
End If
End If
End Sub

 

[Marc L]

As code is a no sense, I asked you to explain, so with words, sentences …

With a crystal clear and complete explanation, it should be easy to help you !
​

 

[NARAYANK991]

Hi ,

Can you explain what your combobox will contain ?

Will it be the text strings Range("B1:B500") , Range("C1:C500") and Range("D1:D500") ?

Narayan

 

[dotchieJack]

Hello,
the combobox will contain textstrings, words like in the first code example
thanks

 

[jindon]

Perhaps

Private Sub CommandButton1_Click()
    Dim temp, i As Long
    temp = ComboBox2.Value
    If IsNumeric(temp) Then temp = Val(temp)
    For i = 2 To 4
        If IsNumeric(Application.Match(temp, Cells(1, i).Resize(500), 0)) Then
            VBA.UserForms.Add("UserForm" & i).Show: Exit For
        End If
    Next
End Sub

 

[dotchieJack]

Hello,
Thank you Jindon, I am almost there
It works wel for coloms c en d but for colom b i get userform 1 instead of userform 2
Thank you in advance

 

[NARAYANK991]

Hi ,

Will this not work ?

Private Sub CommandButton1_Click()
            If ComboBox2.Value = "Range(" & """" & "B1:B500" & """" & ")" Then
               UserForm2.Show
            ElseIf ComboBox2.Value = "Range(" & """" & "C1:C500" & """" & ")" Then
               UserForm3.Show
            ElseIf ComboBox2.Value = "Range(" & """" & "D1:D500" & """" & ")" Then
               UserForm4.Show
            End If
End Sub

Of course , I do not know why you need to have this code within a CommandButton1_Click procedure , when you can have it within a ComboBox2_Change procedure.

Narayan

 

[jindon]

Hello,
Thank you Jindon, I am almost there
It works wel for coloms c en d but for colom b i get userform 1 instead of userform 2
Thank you in advance

I have edited my previous post..., so try again.

 

[dotchieJack]

Hello Narayank,
Thank you for your help, however it does not work

 

[dotchieJack]

Hoi Jindon
Same problem

 

[jindon]

Hoi Jindon
Same problem

That's impossible....
Make sure the code look like.

PrivateSub CommandButton1_Click()
   Dim temp, i AsLong
    temp = ComboBox2.Value
   If IsNumeric(temp) Then temp = Val(temp)
   For i = 2 To 4
       If IsNumeric(Application.Match(temp, Cells(1, i).Resize(500), 0)) Then
            VBA.UserForms.Add("UserForm" & i).Show: ExitFor
       EndIf
   Next
EndSub

was
VBA.UserForms.Add("UserForm" & i + 1).Show: ExitFor

 

[dotchieJack]

Hoi Jindon,
Now i have an error on this line

 Dim temp, i AsLong

 

[dotchieJack]

Hoi Jindon,
I copyed your code again and now it works great
Thank you for all your help