vba weird?

G

Guest

Guest
i have created this code, and for some reason it spawns copies of the worksheet that the first IF selects (January, February etc). If anyone can help, i'd appreciate it.

Private Sub ComboBox2_AfterUpdate()
Dim Month1 As Object
Dim LUList As Object
If Range("MonthChoice") = "January" Then
Sheets("January").Select
ElseIf Range("MonthChoice") = "February" Then
Sheets("February").Select
ElseIf Range("MonthChoice") = "March" Then
Sheets("March").Select
ElseIf Range("MonthChoice") = "April" Then
Sheets("April").Select
ElseIf Range("MonthChoice") = "May" Then
Sheets("May").Select
ElseIf Range("MonthChoice") = "June" Then
Sheets("June").Select
ElseIf Range("MonthChoice") = "July" Then
Sheets("July").Select
ElseIf Range("MonthChoice") = "August" Then
Sheets("August").Select
ElseIf Range("MonthChoice") = "September" Then
Sheets("September").Select
ElseIf Range("MonthChoice") = "October" Then
Sheets("October").Select
ElseIf Range("MonthChoice") = "November" Then
Sheets("November").Select
ElseIf Range("MonthChoice") = "December" Then
Sheets("December").Select
End If
If Range("MonthChoice") = "January" Then
Set Month1 = Range("January1")
ElseIf Range("MonthChoice") = "February" Then
Set Month1 = Range("February1")
ElseIf Range("MonthChoice") = "March" Then
Set Month1 = Range("March1")
ElseIf Range("MonthChoice") = "April" Then
Set Month1 = Range("April1")
ElseIf Range("MonthChoice") = "May" Then
Set Month1 = Range("May1")
ElseIf Range("MonthChoice") = "June" Then
Set Month1 = Range("June1")
ElseIf Range("MonthChoice") = "July" Then
Set Month1 = Range("July1")
ElseIf Range("MonthChoice") = "August" Then
Set Month1 = Range("August1")
ElseIf Range("MonthChoice") = "September" Then
Set Month1 = Range("September1")
ElseIf Range("MonthChoice") = "October" Then
Set Month1 = Range("October1")
ElseIf Range("MonthChoice") = "November" Then
Set Month1 = Range("November1")
ElseIf Range("MonthChoice") = "December" Then
Set Month1 = Range("December1")
ElseIf Range("MonthChoice") = "" Then
answer = MsgBox("You have not selected a Month", vbOKOnly, "ProComp")
End
End If
For Each LUList In Month1
If LUList = Range("DateChoice") Then
LUList.Offset(0, 1).Range("A1").Copy
ActiveSheet.Copy
Sheets("Lists").Select
Range("Taken").Select
ActiveCell.Paste
Else
End If
Next
End Sub


Sometimes it doesnt spawn worksheets. But instead the "ActiveCell.Paste" line doesn't work either. Does anyone know why not?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Also. Does anyone know if the IF sections can be shortened?

Any help is appreciated. Thanks
 
Upvote 0
Private Sub ComboBox2_AfterUpdate()
Dim Month1 As Object
Dim LUList As Object
If Range("MonthChoice") = "January" Then
Sheets("January").Select
ElseIf Range("MonthChoice") = "February" Then
Sheets("February").Select
ElseIf Range("MonthChoice") = "March" Then
Sheets("March").Select
ElseIf Range("MonthChoice") = "April" Then
Sheets("April").Select
ElseIf Range("MonthChoice") = "May" Then
Sheets("May").Select
ElseIf Range("MonthChoice") = "June" Then
Sheets("June").Select
ElseIf Range("MonthChoice") = "July" Then
Sheets("July").Select
ElseIf Range("MonthChoice") = "August" Then
Sheets("August").Select
ElseIf Range("MonthChoice") = "September" Then
Sheets("September").Select
ElseIf Range("MonthChoice") = "October" Then
Sheets("October").Select
ElseIf Range("MonthChoice") = "November" Then
Sheets("November").Select
ElseIf Range("MonthChoice") = "December" Then
Sheets("December").Select
End If
If Range("MonthChoice") = "January" Then
Set Month1 = Range("January1")
ElseIf Range("MonthChoice") = "February" Then
Set Month1 = Range("February1")
ElseIf Range("MonthChoice") = "March" Then
Set Month1 = Range("March1")
ElseIf Range("MonthChoice") = "April" Then
Set Month1 = Range("April1")
ElseIf Range("MonthChoice") = "May" Then
Set Month1 = Range("May1")
ElseIf Range("MonthChoice") = "June" Then
Set Month1 = Range("June1")
ElseIf Range("MonthChoice") = "July" Then
Set Month1 = Range("July1")
ElseIf Range("MonthChoice") = "August" Then
Set Month1 = Range("August1")
ElseIf Range("MonthChoice") = "September" Then
Set Month1 = Range("September1")
ElseIf Range("MonthChoice") = "October" Then
Set Month1 = Range("October1")
ElseIf Range("MonthChoice") = "November" Then
Set Month1 = Range("November1")
ElseIf Range("MonthChoice") = "December" Then
Set Month1 = Range("December1")
ElseIf Range("MonthChoice") = "" Then
answer = MsgBox("You have not selected a Month", vbOKOnly, "ProComp")
End
End If
For Each LUList In Month1
If LUList = Range("DateChoice") Then
LUList.Offset(0, 1).Range("A1").Copy
ActiveSheet.Copy
Sheets("Lists").Select
Range("Taken").Select
ActiveCell.Paste
Else
End If
Next
End Sub

To answer your second quesion first (this may solve the problem, there are two ways I see to remove all of the "If" statements.

The first way is using select case:

<pre>

Select Case MonthChoice

Case January
... your code here

Case December
... etc

Case Else
...This is where the error code for nonselection would go

End Select
</pre>

However it may be easier to just do something like this:

<pre>

If Range("MonthChoice") = "" Then
answer = MsgBox("You have not selected a Month", vbOKOnly, "ProComp")
Else
Sheets(Range("MonthChoice").Value).Select
Set Month1 = Range((Range("MonthChoice").Value) & "1")
End If
</pre>

I'm not quite sure of your syntax, but hopefully you can mess around with this to make it work.

HTH
 
Upvote 0
Thanks. That If code worked. However, i'm still getting the spawning workbook problem though
 
Upvote 0
My new code is this, if anyone can help

Private Sub ComboBox2_AfterUpdate()
Dim Month1 As Object
Dim LUList As Object
If Range("MonthChoice") = "" Then
answer = MsgBox("You have not selected a Month", vbOKOnly, "ProComp")
Else
Sheets(Range("MonthChoice").Value).Select
End If

If Range("MonthChoice") = "" Then
answer = MsgBox("You have not selected a Month", vbOKOnly, "ProComp")
Else
Sheets(Range("MonthChoice").Value).Select
Set Month1 = Range((Range("MonthChoice").Value) & "1")
End If
For Each LUList In Month1
If LUList = Range("DateChoice") Then
LUList.Offset(0, 1).Range("A1").Copy
ActiveSheet.Copy
End If
Next
Sheets("Lists").Select
Range("Taken").Select
ActiveCell.Paste
End Sub
 
Upvote 0
what i want the code to actually do. is to move to a sheet (user selected), go down a list of numbers to find one (user selected), and copy the cell to the right of that number. move to another sheet. and paste into another cell
 
Upvote 0
On 2002-03-03 11:18, Anonymous wrote:
My new code is this, if anyone can help

Private Sub ComboBox2_AfterUpdate()
Dim Month1 As Object
Dim LUList As Object
If Range("MonthChoice") = "" Then
answer = MsgBox("You have not selected a Month", vbOKOnly, "ProComp")
Else
Sheets(Range("MonthChoice").Value).Select
End If

If Range("MonthChoice") = "" Then
answer = MsgBox("You have not selected a Month", vbOKOnly, "ProComp")
Else
Sheets(Range("MonthChoice").Value).Select
Set Month1 = Range((Range("MonthChoice").Value) & "1")
End If
For Each LUList In Month1
If LUList = Range("DateChoice") Then
LUList.Offset(0, 1).Range("A1").Copy
ActiveSheet.Copy
End If
Next
Sheets("Lists").Select
Range("Taken").Select
ActiveCell.Paste
End Sub

What's that "ActiveSheet.Copy" there for? This could be the culprit.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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