listbox Rowsource

ape · Jun 7th 2021

I have a list box with several lines, when I select a row I would like to change the RowSource depending on the value of ListIndex. However the RowSource does not refresh! Here is the code;

Public WS As Worksheet

Public Var1 as Integer

.

Code

Public Sub ListBox1_Click()
Var1 = Me.ListBox1.ListIndex


For Each WS In ThisWorkbook.Worksheets
.
.
.
With Me.ListBox1
.Selected(.ListIndex) = False  'Deselect
.RowSource = WS.Cells(1, 7). Value 'Update RowSource from dynamic variable in 'Cell'
End With


Next WS


End Sub

Display More

**royUK** · Jun 8th 2021

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Jun 8th 2021

Where's the ListBox?

Why are you looping through sheets?

Attach an example workbook

ape · Jun 8th 2021

Hi Roy

I have several work sheets each with a name and a unique reference number that are listed in a list box (hence looping). The code should display the sheet contents after the list box RowSource is updated. This routine works fine from other places within the code but not within the list box itself! I have tried to deselect but it not working. The list box is on a form.

Thank you

**royUK** · Jun 8th 2021

If it's on a userform then why is it a Public Sub?

I asked for a sight of the workbook so I could see where you have placed the code. I also want to know what is actually in the ListBox. This would save me guessing and save us both time!

Maybe

Code

Private Sub ListBox1_Click()
Dim var1 As Integer
var1 = Me.ListBox1.ListIndex

With Me.ListBox1
''///if you clear the ListBox then nothing will happen
'.ListIndex = -1 'Deselect
.RowSource = Worksheets(.Value).Cells(1, 7).Value  'Update RowSource from dynamic variable in 'Cell'
End With


End Sub

Display More

ape · Jun 10th 2021

Hi Roy thanks for responding. Below is the workbook code, not sure if it's of any help. There are lots of other sheets but they contain data only. I have also listed the actual code in the list box. I can update the RowSource from any where else whit in the main from e.g. Combo boxes but not from within the list box itself. The list box simply contains list of work sheet names. This RowSource up date should simply display the data in the selected work sheet.

Code

Sub OpenHeatPumpElecSpec()


Dim Frm As Object


Unload MainForm
Application.ScreenUpdating = True
'Prompt whilst formating sheet
Set Frm = PromptForm
With Frm
.Load
.Caption = ""
.Show vbModeless
.Repaint
End With
'Format Variants (required after updting sheet) and data sheets (required after new data has been entered)
Call HeatPumpDesign.FormatSheets
DoEvents
Unload Frm
Set Frm = Nothing
'Launch form and initialise variant entries
Set Frm = HeatPumpDesign
With Frm
.Load
.Show vbModeless
.ListData.Font.Size = 10
.SystemNum.RowSource = "List"
.Clearform
If Sheets("storage").Range("W9").Value = 0 Then
.DialogueBox.Value = "Data base empty"
Else
.DialogueBox = "System specifications available in data base are displayed in list box below, select system number for electrical requirements"
.ListData.RowSource = "DataBase"
End If
End With
Set Frm = Nothing


End Sub




Actual list box code;


Private Sub ListData_Click()
'Set Listbox RowSource <--- "Display selected sheet"


Dim Sht As Worksheet


Set Sht = Sheets("storage")
If ListData.ListIndex = 0 Then GoTo NotValid
Sht.Range("x10").Value = ListData.ListIndex
With Sht
'Loop through worksheets (ignor named sheets!) to find matching data page number
For Each WS In ThisWorkbook.Worksheets
Select Case WS.Name
Case "Variants", "storage", "Data2", "Help", "Master", "manifold", "Blank"
Case Else
WS.Unprotect Password:="a3839p"
If WS.Cells(1, 6).Value = .Range("x10").Value Then
DialogueBox.Value = "System configuration No." & WS.Cells(1, 6).Value & _
" selected. Electrical requirements is displayed in list box below."
With Me.ListData
.Selected(.ListIndex) = False 'Deselect
.RowSource = WS.Cells(1, 7).Value
End With
SysIndex = WS.Name
GoTo Finish
End If
End Select
Next WS
End With
NotValid:
Finish:
Set Sht = Nothing
End Sub

Display More

**royUK** · Jun 11th 2021

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Jun 11th 2021

I asked for the a sample of the workbook

ape · Jun 11th 2021

Not sure if this is what you want to see?

**royUK** · Jun 11th 2021

That's an image. You need to attach the workbook

ape · Jun 13th 2021

Thank you for your interest but I have worked it out.

**royUK** · Jun 14th 2021

Good that you worked out what it was. To close this thread, please either add your answer and accept it as an answer: this could help others.

Posting your workbook would more than likely got you a better answer judging by the code that I have seen.

listbox Rowsource

Participate now!

Share