vba weird?
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: vba weird?

  1. #1
    Guest

    Default

     
    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?

  2. #2
    Guest

    Default

    Also. Does anyone know if the IF sections can be shortened?

    Any help is appreciated. Thanks

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:



    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


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



    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


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

    HTH


  4. #4
    Guest

    Default

    thanks i'll give it a try

  5. #5
    Guest

    Default

    Thanks. That If code worked. However, i'm still getting the spawning workbook problem though

  6. #6
    Guest

    Default

    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

  7. #7
    Guest

    Default

    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

  8. #8
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  9. #9
    New Member
    Join Date
    Mar 2002
    Location
    England
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    thanks. i changed it to activecell and it worked

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com