Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automate?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automate?
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automate?
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automate?
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 "RR" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automate?
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","") |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automate?
"anon" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need to automate | Excel Programming | |||
Automate without add-ins | Excel Discussion (Misc queries) | |||
Automate Add-in | Excel Discussion (Misc queries) | |||
Trying to automate | Excel Programming | |||
Automate Add-In | Excel Programming |