How to automate?

[RR]

Hi,

i have created drop down in lets say A3(items are apple, orange, banana) AND
A4(From Japan, From China, From USA). If i select apple in A3 i want excel to
automatically pop me "From Japan" in A4. I hope i am clear.


Thanks,
RR

 

[Dan Thompson]

Your question is not totally clear
did you want a drop down menu ebeded in Cell A3 that when you select it
a message pop up comes up that says "From Japan" or whatever or did you want
the "From Japan" to just show up in a cell. It would probably be easier to
do this without Drop down embeded in the spread sheet you are working with.
You can just have all your Produced listed in one column and when you select
one of the cells either have a msgbox pop up with the "From Country" message
or have the "From Country" Part just show up in another cell and than change
when you select say Oranges or somthing. If you want to work with drop down
menus it is much nicer visualy and programaticly to use a Form that just
draws data from a spread sheet.

 

[RR]

Thanks for the reply.

OK.....How do I do that? I want to use drop down menus to select my data. I
dont want msgbox pop up , but rather have change data on the other cell.

Thanks,

 

[Dan Thompson]

Unfortunitly It would take to long for me to explan how to create drop down
menu program and forms and all of that and I don't have the time right now.
I have a program I wrote already long ago that does work and gnerates a form
with a list box based on data in cells however if you do not understand how
to work with forms and interact with them using VBA code it would be
pointless for me to give you the code cause it would not make sense to you. I
will post the code anyway just in case you can figure it out.

Option Explicit
Dim aDlg As Object
Sub DemoListBox() ' exercises the frmCityList Form

Set aDlg = New frmCityList
Load aDlg

'populate the list from outside the form
With aDlg
.lstCity.Clear
.lstCity.RowSource = "A1:A4"
.lstCity.ListIndex = 0 'establish starting selection
End With

aDlg.Show 'display the dialog form

If aDlg.CanceledVar Then
MsgBox prompt:="City selection was canceled.", Title:="List Box Demo",
Buttons:=vbInformation
End If

Unload aDlg
Set aDlg = Nothing
End Sub
Function DispSelection() As String
With aDlg
DispSelection = .lstCity.Value
End With
MsgBox "You chose " & DispSelection & " as your selection"
End Function

The following code is the code you need to put into your form however since
the form you create may be different then this one it may not work for you
but here it is anyway

Option Explicit

Public fCancel As Boolean
Property Get CanceledVar() As Boolean
'Note class modules are used to create your own custom Data Types and Class
Objects.
'Note all form modules are considered to be class modules
'Class modules can not pass or variables (Globaly or Localy) Deffined within
the
'Class module to a standerd Module. However Global variables deffined within
a standerd
'module can be passed to a class module.

'If you wanted you could remove this "Property Get" procedure and define
"CanceledVar"
'as public boolean type in the global section of Module1 and this program
would exicute
'the same. Property Get is only used in this program as an example of using
it in a class
'module

'The Property Get procedure, which creates the user defined read-only
property called
'"CanceledVar" for this class module. Enables your code outside the forms
class module
'(in a Standerd Module) to determin wether the dialog was closed by
clicking the cancel
'button.
CanceledVar = fCancel
End Property
Private Sub btnCancel_Click()
fCancel = True 'cancel button was clicked
Me.Hide
End Sub
Private Sub btnOK_Click()
fCancel = False 'ok button was clicked and dialog was confirmed

'DispSelection '(You can remove the rem infront of this function call to
use the
' DispSelection Function which preforms the same thing as
the following
' Line of code. The DispSelection Function is just added
to show an
' example of using functoins or procedures outside of the
form class
' module procedures or functions)

MsgBox "You chose " & Me.lstCity.Value & " as your selection"
End Sub
Private Sub lstCity_Click()
With Me
.lblCity.Caption = .lstCity.Value
End With
End Sub

 

[anon]

I may have understood this wrongly, but if it's that simple,

In A3 set data validation to list, and to a set of cells containg the
values, Apple, Orange etc
In a4 set a formula =if(a3="Apple","From Japan",if(a3="Orange","From
Spain","")

 

[RR]

I may have understood this wrongly, but if it's that simple,

In A3 set data validation to list, and to a set of cells containg the
values, Apple, Orange etc
In a4 set a formula =if(a3="Apple","From Japan",if(a3="Orange","From
Spain","")

Thanks but its little complex than that. I just want ----- If a3=apple than
a4 should be from japan. Rest i dont care. But if i input formula in cell A3
it has to apply to all the cells in A4. I dont want that. I just want one to
data in A3 to relate to one data in A4.

Thanks.
RR