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
|
Similar Threads
