Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

developed a VBA code in order select a choice from a dropdown list

  Asked By: Kuhaylah    Date: Nov 12    Category: MS Office    Views: 12134
  

i have a problem, i developed a VBA code in order select a choice
from a dropdown list (comboBOX) , if i chose 1 , then i will go to range
("a1"), and if i chose 2 i will go to range("w1") and if i chose 3 i
will go to range("AT1"), and after that i have another dropdown list
(combobox) in the same worksheet, if i chose 1 i will go to Range
("A100") , and if i choose 2 i will go range("w100"), and so on,

and for evety dr4opdown list i assigned a macro doing that , i need to
merge these two macro's in one as following

if the drop down name is "dropdown 9" the do like first dropdown.
else if the dropdown was "dropdown 10 " then do the second part.

Share: 

 

13 Answers Found

 
Answer #1    Answered By: Brock Smith     Answered On: Nov 12

You don't say if these dropdowns are on a worksheet  or on a Userform.

If you have these dropdowns on a Userform you could check the name
property of the dropdown  and return that.

If you have these dropdoens on a worksheet, well I do not know how to
reference these dropdowns, so move them to a Userform ;-)

 
Answer #2    Answered By: Sheri Porter     Answered On: Nov 12

This is possible. You have to tie each DropDown_change event to a common macro

Thus if your checking code  is a macro  named CheckDropDown in module1 then

Sub DropDown2_Change()
Module1.CheckDropDown
End Sub
'If there is aother drop  down to check then add

Sub DropDown3_Change()
Module1.CheckDropDown
End Sub
'''''Go on adding the drop down change events

'Now the code in CheckDropDown will be like this
'This code is added in Module1


Sub CheckDropDown()

For Each myshape In ActiveSheet.Shapes

'The following code will check for a Shape called Drop Down 2
If myshape.Name = "Drop Down 2" Then
'Do your code here
'You can even Try a case select  statement

End If

Next

End Sub

 
Answer #3    Answered By: Ibadah Younis     Answered On: Nov 12

Thank you for your help, and every one tried to help me.

Unfortunately Neville, your code  is the same which i tried first, as
following

if sheets(activesheet.name).myshape.name="drop dwon 9" then
..........

Nt working and give me run time error 438
object dosen't support this property or method.

and also give me a note that (myshape=empty)...what is myshape mean
and is it equal to shape???
i dont know what to do.

 
Answer #4    Answered By: Raymond Fischer     Answered On: Nov 12

I think we need to see the whole of your sub to understand the problem.
Please copy it and paste it into an email (don't retype bits of it).

Also do you have "Option explicit" at the top of the module where the sub is?
If not you should put it there, right at the beginning above all the code.
Option explicit makes sure you have declared all the variables you are using.

The code

if sheets(activesheet.name).myshape.name="drop dwon 9" then

should be (I think)

if sheets(activesheet.name).myshape.name="DropDown9" then

This assumes that you have declared "myshape" as an object or as a shape using

Dim myshape as Shape

and then set it to something using

Set myshape = something

or you could have used it in a for next loop like

For Each myshape In ActiveSheet.Shapes

If we see your code  we will be more able to help.

 
Answer #5    Answered By: Sebastian Anderson     Answered On: Nov 12

I am sorry I did not
include the explicit def. I wrote the code  on the fly just to solve the checking
of the name of the shapes specifically to answer question of how to
check for the shape names and based on this criteria, how do you go about
solving the problem.

 
Answer #6    Answered By: Inez Wood     Answered On: Nov 12

OK. Sorry for butting in. Should have looked back at the whole thread.

 
Answer #7    Answered By: Natasha Rivera     Answered On: Nov 12

thank you all for helping me ... this is my code

Sub First_list()
dim myshape is shape

If Sheets(ActiveSheet.Name).myShape.Name = "Drop Down 9" Then
If Range("u17") = 1 Then
Sheets(ActiveSheet.Name).Select
Application.Goto Reference:=Worksheets(ActiveSheet.Name).Range
("A1"), _
Scroll:=True
ElseIf Range("u17") = 2 Then
Sheets(ActiveSheet.Name).Select
Application.Goto Reference:=Worksheets(ActiveSheet.Name).Range
("W1"), _
Scroll:=True
ElseIf Range("u17") = 3 Then
Sheets(ActiveSheet.Name).Select
Application.Goto Reference:=Worksheets(ActiveSheet.Name).Range
("AT1"), _
Scroll:=True
End If


ElseIf Worksheets(ActiveSheet.Name).myShape.Name = ("Drop Down 464")
Then
If Range("u17") = 1 Then
Sheets(ActiveSheet.Name).Select
Application.Goto Reference:=Worksheets(ActiveSheet.Name).Range
("A70"), _
Scroll:=True
ElseIf Range("u17") = 2 Then
Sheets(ActiveSheet.Name).Select
Application.Goto Reference:=Worksheets(ActiveSheet.Name).Range
("W70"), _
Scroll:=True
ElseIf Range("u17") = 3 Then
Sheets(ActiveSheet.Name).Select
Application.Goto Reference:=Worksheets(ActiveSheet.Name).Range
("AT70"), _
Scroll:=True
End If

End If


End Sub


it gives me the erorr which i told before :

run time error 438
object dosen't support this property or method.

 
Answer #8    Answered By: Saila Iqbal     Answered On: Nov 12

First two obvious errors are:

dim myshape is shape
should be
dim myshape as shape

Object names cannot have spaces in them so

"Drop Down 9"

should be (probably) "DropDown9"

If any line is red in the VBA editor it has an error and needs to be
corrected.

Then click debug/compile in the VBA editor menu and it will tell you other
errors. When these are corrected and it compiles correctly you try running
it. There may be runtime errors.

 
Answer #9    Answered By: Wallace Kelly     Answered On: Nov 12

Please change your code  to this

Sub First_list()
Dim myshape As Shape
For Each myshape In ActiveSheet.Shapes
If myshape.Name = "Drop Down 9" Then
If Range("u17") = 1 Then
Sheets(ActiveSheet.Name).Select
Application.Goto Reference:=Worksheets(ActiveSheet.Name).Range("A1"), _
Scroll:=True
ElseIf Range("u17") = 2 Then
Sheets(ActiveSheet.Name).Select
Application.Goto Reference:=Worksheets(ActiveSheet.Name).Range("W1"), _
Scroll:=True
ElseIf Range("u17") = 3 Then
Sheets(ActiveSheet.Name).Select
Application.Goto Reference:=Worksheets(ActiveSheet.Name).Range("AT1"), _
Scroll:=True
End If

ElseIf myshape.Name = "Drop Down 464" Then
If Range("u17") = 1 Then
Sheets(ActiveSheet.Name).Select
Application.Goto Reference:=Worksheets(ActiveSheet.Name).Range("A70"), _
Scroll:=True
ElseIf Range("u17") = 2 Then
Sheets(ActiveSheet.Name).Select
Application.Goto Reference:=Worksheets(ActiveSheet.Name).Range("W70"), _
Scroll:=True
ElseIf Range("u17") = 3 Then
Sheets(ActiveSheet.Name).Select
Application.Goto Reference:=Worksheets(ActiveSheet.Name).Range("AT70"), _
Scroll:=True
End If
End If
Next
End Sub

 
Answer #10    Answered By: Zack Thompson     Answered On: Nov 12

first of all i would thank all of you for that team work :)

Second i change my code  to what you sent to me ... it's worked in
compiling, BUT the result NOT GOOD, the code only applay to drop  down
464 and not applied for dropdown  9, in other word it apply only for
the second part  of the code for dropdown 464 , and if i change the
second part to be dropdown 9 , it will work for dropdown 9 only and
ignore the dropdwon in the first part, i hope that i explained the
issue very well.

 
Answer #11    Answered By: Krista Warren     Answered On: Nov 12

Please give me your code  exactly..............

 
Answer #12    Answered By: Aakifah Khan     Answered On: Nov 12

This is all of my code, only that, No MORE.............

 
Answer #13    Answered By: Chelsea Rose     Answered On: Nov 12

The code  I have given you seems to work fine for me.

I just forgot to include this statement before the For each statement.

Dim myshape as Shape

Then everything will be fine.

 




Tagged: