vba weird?

  • Thread starter Thread starter Guest
  • Start date Start date
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
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
Click to expand...

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
Click to expand...

What's that "ActiveSheet.Copy" there for? This could be the culprit.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Calc cumulative by month
Replies
6
Views
194
mylamly26
M
M
  • Solved
Nested Xlookup not returning all data
Replies
2
Views
431
Mat1968
M
S
  • Question Question
Employee Training Tracker
Replies
0
Views
223
Schuyla
S
K
  • Question Question
Copy data from vertical cells paste into horizontal cells that will change each time macro run
Replies
1
Views
128
sxhall
S
J
  • Question Question
Using index and match from another source excel file
2
Replies
10
Views
627
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,370
Messages
6,171,686
Members
452,418
Latest member
kennettz

Share this page

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