Modify code to work in 2 listboxes.

danbates

Active Member
Joined
Oct 8, 2017
Messages
377
Office Version
  1. 2016
Platform
  1. Windows
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:

Code:
[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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Put this at the top of your code
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
Code:
Sheets(Sht).UsedRange.Copy
 
Upvote 0
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:

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

Any ideas?

Thanks

Dan
 
Upvote 0
Change the initialize code to
Code:
   If i <= 26 Then
      Me.ListBox1.ListIndex = (i - 1)
   Else
      Me.Listbox2.ListIndex = (i - 27)
   End If
 
Upvote 0
Hi Fluff,

I seem to of found a solution.

Do this look ok to you?

Code:
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
 
Upvote 0
If it works, then it's fine. :)
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top