Modify code to work in 2 listboxes.

[danbates]

Hi,

Previously I had 1 listbox with the 52 weeks of the year. Listed WK 1 to WK 52.
I have change my userform now because the listbox was too long.
I now have 2 listboxes.

Listbox1 from WK 1 to WK 26.
ListBox2 from WK 27 to WK 52.

I have the following code that worked when it was just the 1 listbox:

[COLOR=#333333]Private Sub CommandButton1_Click()[/COLOR]
With Application
    .ScreenUpdating = False
    .Calculation = xlManual

With Sheets("Current weeks data")
    .Cells.ClearContents
    Sheets(ListBox1.Value).UsedRange.Copy
    .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    .UsedRange.Columns.Autofit
End With

    .Calculation = xlAutomatic
    .ScreenUpdating = True
End With
 [COLOR=#333333]End Sub[/COLOR]

I now need the code modifying so it works in ListBox2 as well.

Any help would be appreciated.

Thanks

Dan

 

[Fluff]

Put this at the top of your code

   Dim Sht As String
   Select Case Me.ListBox1.ListIndex
      Case -1:    Sht = Me.Listbox2.Value
      Case Else:  Sht = Me.ListBox1.Value
   End Select

And use

Sheets(Sht).UsedRange.Copy

 

[danbates]

Hi Fluff,

I've added your code and it works if I select any week number in one of the listboxes but if I open the userform and with your code that highlights the current week, when I press the command button without selecting anything from the listboxes I get the following error:

Run time error 9 - Subscript out of range

and it highlights this part of your code:

[COLOR=#333333]Sheets(Sht).UsedRange.Copy[/COLOR]

Any ideas?

Thanks

Dan

 

[Fluff]

Change the initialize code to

   If i <= 26 Then
      Me.ListBox1.ListIndex = (i - 1)
   Else
      Me.Listbox2.ListIndex = (i - 27)
   End If

 

[danbates]

Hi,

I'm afraid it's still the same outcome.

Thanks

Dan

 

[danbates]

Hi Fluff,

I seem to of found a solution.

Do this look ok to you?

Private Sub UserForm_Initialize()   

i = Application.WeekNum(Date)

If i <= 26 Then
      ListBox1.SetFocus
      Me.ListBox1.ListIndex = (i - 1)
Else
     ListBox2.SetFocus
     Me.ListBox2.ListIndex = (i - 27)
End If


End Sub

Thanks again

Dan

 

[Fluff]

If it works, then it's fine.