Listbox

[Gutierrez]

I have a listbox on a worksheet that after i add list items doesnt display ... any help would be appreciated .. i do know that the listbox is populated .. if i minimize sheet and then go back to it .. its there anyway to do this in vba?


Public Sub Workbook_Open()

ActiveSheet.Cells(6, 8).Value = ""

Dim i As Long
Dim xTemp As Workbook
Dim xFundName As String
Dim x As Long
Dim y As Long
Dim RetVal As Variant

Range("g7") = Application.InputBox(prompt:="Please Enter Fund Name", Title:="Fund Name", Type:=2)

xFundName = ActiveSheet.Cells(7, 7)

Application.DisplayAlerts = False
Application.ScreenUpdating = False

ActiveSheet.ListBox1.Clear

Set xTemp = Workbooks.Open("P:\Proj\admin\" & xFundName & "\" & xFundName & "_security-master.xls")

i = 1

Do Until i = 30
If Cells(6, i) = "" Then
Else
ThisWorkbook.Sheets("SHEET1").ListBox1.AddItem xTemp.ActiveSheet.Cells(6, i).Value
End If
i = i + 1
Loop

xTemp.Close

RetVal = MsgBox("Would you like to add grouping?", vbQuestion + vbYesNo)
If RetVal = vbYes Then
x = 1
ActiveSheet.ListBox1.Visible = True
ActiveSheet.ListBox1.MultiSelect = fmMultiSelectSingle

ActiveSheet.Cells(6, 8) = "Select Grouping / Field(s)"
With ActiveSheet.Cells(6, 8).Font
.Bold = True
.Name = "Trebuchet MS"
.Size = 10
End With
Else
ActiveSheet.ListBox1.Visible = True
ActiveSheet.ListBox1.MultiSelect = fmMultiSelectMulti

ActiveSheet.Cells(6, 8) = "Select Grouping / Field(s)"
With ActiveSheet.Cells(6, 8).Font
.Bold = True
.Name = "Trebuchet MS"
.Size = 10
End With

x = 2
End If

Select Case x
Case 1
RetVal = MsgBox("Would you like to show subtotals?", vbQuestion + vbYesNo)
If RetVal = vbYes Then
y = 1
Else
y = 2
End If
Case 2
y = 2
End Select

ActiveSheet.Cells(1, 1).Value = y
ActiveSheet.Cells(2, 1).Value = x

MsgBox ("Please Input Period Data Before Running Macro. Thank you.")

Exit Sub

 

[SkipVought]

Hi,

I advise against using the Select, Activate, ActiveSheet, ActiveCell.

    ActiveSheet.Cells(6, 8).Value = ""
    
    Dim i As Long
    Dim xTemp As Workbook
    Dim xFundName As String
    Dim x As Long
    Dim y As Long
    Dim RetVal As Variant
    
    Range("g7") = Application.InputBox(prompt:="Please Enter Fund Name", Title:="Fund Name", Type:=2)
    
    xFundName = ActiveSheet.Cells(7, 7)

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
'specify the sheet in this workbook
    [red][b]ActiveSheet[/b][/red].ListBox1.Clear
    
    Set xTemp = Workbooks.Open("P:\Proj\admin\" & xFundName & "\" & xFundName & "_security-master.xls")
    
    i = 1
            
    Do Until i = 30
        If Cells(6, i) = "" Then
        Else
'specify the sheet in workbook xTemp
            ThisWorkbook.Sheets("SHEET1").ListBox1.AddItem xTemp.[red][b]ActiveSheet[/b][/red].Cells(6, i).Value
       End If
        i = i + 1
    Loop
 ...

Skip,

_[red][/red]

 

[Gutierrez]

Still need help with this one folks ... I actually posted again .. sorry.

 

[combo]

Follow Skip's advice. To debug, add Stop to your code:

Public Sub Workbook_Open()
Stop
ActiveSheet.Cells(6, 8).Value = ""
...

combo