UserForms and ranges. How to link userform values

[eggyboy]

--------------------------------------------------------------------------------

Hi everyone!

I'm fairly new to the whole VB thing but i've got a problem so this is the first place i decided to go...

What i've got at the moment is a spreadsheet that display a series of issues and the tests that they are linked to (see zipped attachment).

What I want to do is create a userform that has two ListBoxes. So that when an Issue is selected in the first list box the tests that it is linked to appear in the second list box.

For example with the attachment. User selects Issue 1 (in the first list box)and Tests 1 & Test 2 are displayed in the second list box, and where possible reverse the process.

Anyone got any ideas how i can write this in VB. Preferably so that it can also be exported to a grid or somewhere

Any help will be much appreciated!

 

[dstrat6]

Hi eggyboy in case you haven't figured out your problem on your own. I did some testing myself and I think this is what you were trying to accomplish.
EXAMPLE:

Option Explicit

Private Sub UserForm_Click()

   'Clear the list before you add new items
   ListBox1.Clear
   
   'Add the items you want
   ListBox1.AddItem ("Issue 1")
   ListBox1.AddItem ("Issue 2")
   ListBox1.AddItem ("Issue 3")
   ListBox1.AddItem ("Issue 4")
   ListBox1.AddItem ("Issue 5")
   
End Sub

Private Sub ListBox1_Click()
  Dim s1 As String
  Dim s2 As String
  Dim s3 As String
  Dim s4 As String
  Dim s5 As String
  Dim ws As Worksheet
  
On Error GoTo ErrorHandler

  'Create Variables
  s1 = "Issue 1"
  s2 = "Issue 2"
  s3 = "Issue 3"
  s4 = "Issue 4"
  s5 = "Issue 5"
  
  Set ws = ActiveSheet  'Set the active sheet to retrieve your values
  
  If ListBox1.Text = s1 Then
    ListBox2.AddItem ws.Range("A1")
  End If
  
  If ListBox1.Text = s2 Then
    ListBox2.AddItem ws.Range("A2")
  End If
  
  If ListBox1.Text = s3 Then
    ListBox2.AddItem ws.Range("A3")
  End If
  
  If ListBox1.Text = s4 Then
    ListBox2.AddItem ws.Range("A4")
  End If
  
  If ListBox1.Text = s5 Then
    ListBox2.AddItem ws.Range("A5")
  End If
  
Exit Sub
ErrorHandler:
    MsgBox Err.Description, vbCritical, "Error"
End Sub

Private Sub cmdExit_Click()
   UserForm1.Hide
End Sub

Private Sub cmdClear_Click()
   ListBox1.Clear
   ListBox2.Clear
End Sub

Hope this helps, this code will go into your user form. Then call the form either with a button or Worksheet_SelectionChange.

Regards,
dstrat

 

[fumei]

Rather than 5 separate, and executed, instructions using multiple If...Then statements, as the testing is for ONE thing (Listbox1.Text), it would be better to use Select Case.

Select Case ListBox1.Text
   Case s1
     ListBox2.AddItem ws.Range("A1")
   Case s2
     ListBox2.AddItem ws.Range("A2")
   Case s3
     ListBox2.AddItem ws.Range("A3")
   Case s4
     ListBox2.AddItem ws.Range("A4")
   Case s5
     ListBox2.AddItem ws.Range("A5")
End select

Using multiple If...Then means that:

If ListBox1.Text = s1

the other If...Then instructions are still executed.

faq219-2884

Gerry
My paintings and sculpture

 

[dstrat6]

Hi fumei, thanks for the heads up I've never really used Select statements. Maybe it's time I start practicing.

Regards,
Dstrat

 

[fumei]

Oh my,.....ummmm, yes indeed.

faq219-2884

Gerry
My paintings and sculpture