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
.
.
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
.
.
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.
Where's the ListBox?
Why are you looping through sheets?
Attach an example workbook
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
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
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
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.
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
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.
I asked for the a sample of the workbook
That's an image. You need to attach the workbook
Thank you for your interest but I have worked it out.
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.
Don’t have an account yet? Register yourself now and be a part of our community!