PDA

View Full Version : multiple comboboxes



margu
03-17-2015, 03:07 AM
Hi,
I am new in forum and I need help :) I have a word document (word 2003), with tables. I want to fill tables, using data from excel workbook (excel 2003). There are 20 tables with 15 rows each approximately.
In every row I want to add and combobox (activeX), where user can choose an mark from 1 to 5. This event will change an label in the same row, called condition, form "very good" to "very poor". Then next combobox will display 3 options from excel sheet. I have 1 row ready :) And I would like to generalize it.
I read about classes, but I am not sure, if I can use it for activeX combobox. And I read something about OleObejcts and for each option.
I am learning VBA on my own, it's not my profession, sorry for my mistakes.

Thank you very much,
Marianna

Private Sub ComboBox2_DropButtonClick()
Dim strSelected As String
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim line As Long
Dim start As Long
Dim remarks As Long

If ComboBox2.ListIndex > -1 Then
strSelected = ComboBox2.List(ComboBox2.ListIndex)
End If

If ComboBox2.ListCount > 0 Then
Me.ComboBox2.Clear
End If

With Me.ComboBox2
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "NS"
.ListIndex = 0
End With
ComboBox2.Value = strSelected

Select Case ComboBox2.ListIndex
Case 0
Label2.Caption = "Very good"
Case 1
Label2.Caption = "Good"
Case 2
Label2.Caption = "Saticfactory"
Case 3
Label2.Caption = "Sufficient"
Case 4
Label2.Caption = "Poor"
Case 5
Label2.Caption = "Very poor"
Case 6
Label2.Caption = "Not seen"
End Select

Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open(FileName:="C:\Users\...\test.xlsx")
Set ws = wb.Worksheets(1)
Me.ComboBox3.Clear
remarks = 5
Select Case Label2.Caption
Case "Very good"
start = 4
For line = 1 To 3
Me.ComboBox3.AddItem ws.Cells(line + start, remarks)
Next line
Me.ComboBox3.ListIndex = 0

Case "Good"
start = 7
For line = 1 To 3
Me.ComboBox3.AddItem ws.Cells(line + start, remarks)
Next line
Me.ComboBox3.ListIndex = 0

Case "Saticfactory"
start = 10
For line = 1 To 3
Me.ComboBox3.AddItem ws.Cells(line + start, remarks)
Next line
Me.ComboBox3.ListIndex = 0

Case "Sufficient"
start = 13
For line = 1 To 3
Me.ComboBox3.AddItem ws.Cells(line + start, remarks)
Next line
Me.ComboBox3.ListIndex = 0

Case "Poor"
start = 16
For line = 1 To 3
Me.ComboBox3.AddItem ws.Cells(line + start, remarks)
Next line
Me.ComboBox3.ListIndex = 0

Case "Very poor"
start = 19
For line = 1 To 3
Me.ComboBox3.AddItem ws.Cells(line + start, remarks)
Next line
Me.ComboBox3.ListIndex = 0

Case "Not seen"
start = 22
For line = 1 To 3
Me.ComboBox3.AddItem ws.Cells(line + start, remarks)
Next line
Me.ComboBox3.ListIndex = 0
End Select

wb.Close
xlApp.Quit
Set xlApp = Nothing
End Sub

Private Sub ComboBox3_Change() ' I had to add an Textbox too, because combobox doesn't display a long text
With Me.TextBox1
.Width = 235.8
.MultiLine = True
.WordWrap = True
.Text = ComboBox3.Value
.AutoSize = True
End With
End Sub