Use makro to change information

[Magnus]

Hi everyone!
I just started to use makro and after two hours work I'm still totally lost.
So my wounders are if there is possible to change information in one
tabel-cell depending on wich alternative a choose in a dropdown-menu?
Like if I clik on barbeque in the dropdown-menu another dropdown-menu will
show in the other cell where I only can choose barbeque recepies and after I
clicked on one of those it will show the recepie in the second cell instead
of the second dropdownmenu.

I know this may sound a bit confusing but please help me.

/Magnus

 

[Doug Robbins]

Here is some code the first part of which (part of the UserForm_Initialize()
sub)adds the items to a combobox called comboDocGroup and the second then
loads a listbox - ListDocbyTitle, (or it could be a combobox) with the
documents belonging to the document group selected in the comboDocGroup.

For this to work, the items for the document groups are stored one to a row
in a two column table in a document that in this case has the filename RNM
Documents.doc and the documents that belong to each document group are
stored in separate paragraphs in the cell in the column to the right of the
cell that contains the document group to which they belong.

In your case that table would be set up as

Recipe Type Recipes
Barbecue Barbecue Recipe 1
Barbecue Recipe 2
etc
___________________________
Desserts Desert Recipe 1
Desert Recipe 2
etc
___________________________
etc etc
etc
___________________________



Set sourcedoc = Documents.Open(PathofSystemFiles & "\RNM Documents.doc",
, , False)
For i = 2 To sourcedoc.Tables(1).Rows.Count
Set myitem = sourcedoc.Tables(1).Cell(i, 1).Range
myitem.End = myitem.End - 1
'MsgBox myitem
comboDocGroup.AddItem myitem.Text
Next i
sourcedoc.Close wdDoNotSaveChanges


Private Sub comboDocGroup_Change()
' Procedure to populate the list box with the documents included in the
selected group
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range, m As
Long, n As Long
'Clear existing data that may be displayed
txtDateReleased = ""
txtDocReviewerBy = ""
txtDocApprovedBy = ""
listDocByTitle.Clear
Application.ScreenUpdating = False
Set sourcedoc = Documents.Open(PathofSystemFiles & "\RNM Documents.doc",
, , False)
'Get the number or Documents for the Group selected = number of
paragraphs in
'the cell in column 2 of the table for that row that corresponds to the
chosen Doc Group
'less one
i = sourcedoc.Tables(1).Cell(comboDocGroup.ListIndex + 2,
2).Range.Paragraphs.Count
' Define an array to be loaded with the Document data
Dim MyArray() As Variant
'Load client data into MyArray
ReDim MyArray(i, 2)
For n = 0 To 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(comboDocGroup.ListIndex +
2, n + 2).Range.Paragraphs(m + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
sourcedoc.Close wdDoNotSaveChanges
Application.ScreenUpdating = True
' Load data into ListDocByTitle
listDocByTitle.List() = MyArray
' Disable the cmdUpdateSelectedDoc button until the user opens a document
cmdUpdateSelectedDoc.Enabled = False
CmdViewDocument.Enabled = False
cmdCloseDocument.Enabled = False
FrameRevisionDetails.Visible = False
cmdRevisionHistory.Visible = False
ListRevisionHistory.Visible = False
End Sub


--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[Helmut Weber]

Hi Magnus,

apart from Doug's impressive code,
here comes a demo, tested in forms design,
for filtering the contents of list (2)
depending on what was selected in list (1),
which is a functionality every database engine has or should have
built in. It is, only in principle, as simple as this:
Built a list (1), here containing the characters A ... Z.
Built a list (2), here it is AA ... ZZ.
If in list(1) "A" is selected, get all entries in list(2),
which start with "A" and display them.
Not quite simple, though.

Option Explicit
' 26 by 26 characters
Dim sFilter(676) As String

Private Sub ComboBox1_Click()
Dim i As Integer
Dim s As String
ComboBox2.Clear
s = ""
' filter list 2
For i = 1 To 676
If Left(sFilter(i), 1) = ComboBox1.Text Then
ComboBox2.AddItem sFilter(i)
If s = "" Then s = sFilter(i)
End If
Next
ComboBox2.Text = s
End Sub

Sub Test()
' to get it all going
Dim i As Integer
Dim j As Integer
Dim k As Integer
k = 0
ComboBox1.Clear
' fill list 1 with A ... Z
For i = 65 To 90
ComboBox1.AddItem Chr(i)
Next
ComboBox1.Text = Chr(65)
' fill list 2 with AA ... ZZ
For i = 65 To 90
For j = 65 To 90
k = k + 1
sFilter(k) = Chr(i) & Chr(j)
Next
Next
End Sub

Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[fumei]

Wow you guys like to do things a bit wild. There was no statement I could
see mentioning a userForm, so code on the UserForm_Initialize may not be
relevant.

This is a design issue, rather than a code issue.

It can be done with Form drop downs, but may be easier with ActiveX dropdpwn
controls.

Drop down 1 has a list. Dropdown2 is also a list.

Have a number of arrays for possible population of dropdown 2.
DropDown2Array_1()
DropDown2Array_2()
.....etc...


A simple Select Case will populate dropdown2.

Dim i As Integer
Select Case Dropdown1.Value ' // or text
Case ListItem1
Dropdown2.Clear
For var = 0 to Ubound(DropDown2Array_1(i)
DropDown2.AddItem DropDown2Array_1(i)
i = i + 1
Case ListItem2
Dropdown2.Clear
For var = 0 to Ubound(DropDown2Array_2(i)
DropDown2.AddItem DropDown2Array_2(i)
i = i + 1
....etc...
End Select

That will clear the second dropdown, then repopulate with the arrays that
match.

as for the cell getting the result of dropdown2, simply use the current
value (after repopulation) of Dropdown2 to either:

open a full blown recipe document, based on the value of Dropdown2; OR, get
text from an AutoText. This depends, again, on design issue, not code issues.