+ Reply to Thread
Results 1 to 11 of 11
  1. 11-23-2009, 09:53 AM #1
    Domintor2992
    Domintor2992 is offline
    Registered User
    Join Date
    11-23-2009
    Location
    home
    MS-Off Ver
    Excel 2007
    Posts
    6

    Question vba coding... IF, Then, Goto "multi workbooks"

    i need help with this if statement.
    i want excel to look at what text is in the combo box on my user form, depending on what it is i want it to move me to another workbook and a specific sheet within that workbook.

    the code i have tried myself is...

    Code:
    Sub CommandButton1_Click()
If ComboBox1 = "Old Trafford Tour(Manchester)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 1").Range ("A1")
Else
If ComboBox1 = "Sister Act(London)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 2").Range ("A1")
Else
If ComboBox1 = "The Lion King(London)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 3").Range ("A1")
Else
If ComboBox1 = "Christmas Shopping(London)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 4").Range ("A1")
Else
If ComboBox1 = "Edinburgh Festival(Edinburgh)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 5").Range ("A1")
Else
If ComboBox1 = "The Gadget Show Live(Birmingham)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 6").Range ("A1")
Else
If ComboBox1 = "Disneyland Paris(Paris)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 7").Range ("A1")
Else
If ComboBox1 = "Port Aventura(Barcelona)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 8").Range ("A1")
Else
If ComboBox1 = "Booze Crooze(Dover)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 9").Range ("A1")
Else
If ComboBox1 = "Pleasurewood Hills(Great Yasrmouth)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 10").Range ("A1")
Else
If ComboBox1 = "Amsterdam School Trip(Amsterdam)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 11").Range ("A1")
Else
If ComboBox1 = "Olympic Village School Trip(London)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 12").Range ("A1")
Else
If ComboBox1 = "Celebrity Dancing On Ice(Nottingham)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 13").Range ("A1")
Else
If ComboBox1 = "X Factor Tour(Cardiff)" Then
GoTo_Workbook("Bookings").Worksheets("Trip 14").Range ("A1")
Else
If ComboBox1 = "Alton Towers Halloween Weekend" Then
GoTo_Workbook("Bookings").Worksheets("Trip 15").Range ("A1")
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub

    I nned this for my a level project so if you can get back to me asap i would be grateful.
    Cheers
    Last edited by Domintor2992; 11-25-2009 at 10:46 AM. Reason: forum rules
    Reply With Quote Reply With Quote
  2. 11-23-2009, 10:26 AM #2
    davesexcel
    davesexcel is offline
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Cochrane,Alberta
    MS-Off Ver
    XL 2003,2007
    Posts
    6,259

    Re: vba coding... IF, Then, Goto "multi workbooks"

    Does it work?
    Replace the "else" with "Elseif", then you would only require one "End If"
    You may need to dim the workbook, at the beggining

    Untested.

    Code:
         Dim wb As Workbook
    Set wb = Workbooks("Bookings")
    If ComboBox1 = "Old Trafford Tour(Manchester)" Then
        GoTo_wb.Worksheets("Trip 1").Range ("A1")
    ElseIf ComboBox1 = "Sister Act(London)" Then
        GoTo_wb.Worksheets("Trip 2").Range ("A1")
    ElseIf ComboBox1 = "The Lion King(London)" Then
        GoTo_wb.Worksheets("Trip 3").Range ("A1")
    ElseIf ComboBox1 = "Christmas Shopping(London)" Then
        GoTo_wb.Worksheets("Trip 4").Range ("A1")
    ElseIf ComboBox1 = "Edinburgh Festival(Edinburgh)" Then
        GoTo_wb.Worksheets("Trip 5").Range ("A1")
    ElseIf ComboBox1 = "The Gadget Show Live(Birmingham)" Then
        GoTo_wb.Worksheets("Trip 6").Range ("A1")
    ElseIf ComboBox1 = "Disneyland Paris(Paris)" Then
        GoTo_wb.Worksheets("Trip 7").Range ("A1")
    ElseIf ComboBox1 = "Port Aventura(Barcelona)" Then
        GoTo_wb.Worksheets("Trip 8").Range ("A1")
    ElseIf ComboBox1 = "Booze Crooze(Dover)" Then
        GoTo_wb.Worksheets("Trip 9").Range ("A1")
    ElseIf ComboBox1 = "Pleasurewood Hills(Great Yasrmouth)" Then
        GoTo_wb.Worksheets("Trip 10").Range ("A1")
    ElseIf ComboBox1 = "Amsterdam School Trip(Amsterdam)" Then
        GoTo_wb.Worksheets("Trip 11").Range ("A1")
    ElseIf ComboBox1 = "Olympic Village School Trip(London)" Then
        GoTo_wb.Worksheets("Trip 12").Range ("A1")
    ElseIf ComboBox1 = "Celebrity Dancing On Ice(Nottingham)" Then
        GoTo_wb.Worksheets("Trip 13").Range ("A1")
    ElseIf ComboBox1 = "X Factor Tour(Cardiff)" Then
        GoTo_wb.Worksheets("Trip 14").Range ("A1")
    ElseIf ComboBox1 = "Alton Towers Halloween Weekend" Then
        GoTo_wb.Worksheets("Trip 15").Range ("A1")
    End If
    Dave

    Reply With Quote Reply With Quote
  3. 11-23-2009, 12:20 PM #3
    Domintor2992
    Domintor2992 is offline
    Registered User
    Join Date
    11-23-2009
    Location
    home
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: vba coding... IF, Then, Goto "multi workbooks"

    thanks for your attempt but when i copied your code into my spreadsheets and ran the user form, the button that this action is supposed to occur on didnt do anything.
    Reply With Quote Reply With Quote
  4. 11-23-2009, 08:27 PM #4
    davesexcel
    davesexcel is offline
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Cochrane,Alberta
    MS-Off Ver
    XL 2003,2007
    Posts
    6,259

    Re: vba coding... IF, Then, Goto "multi workbooks"

    Quote Originally Posted by Domintor2992 View Post
    thanks for your attempt but when i copied your code into my spreadsheets and ran the user form, the button that this action is supposed to occur on didnt do anything.
    I believe you need to the word "Application"

    Code:
        Dim wb As Workbook
    Set wb = Workbooks("Book2")
    
    If ComboBox1 = "May" Then
        Application.Goto wb.Worksheets("Sheet1").Range("A1")
    ElseIf ComboBox1 = "June" Then
        Application.Goto wb.Worksheets("Sheet2").Range("A1")
    End If
    Dave

    Reply With Quote Reply With Quote
  5. 11-24-2009, 08:04 AM #5
    Domintor2992
    Domintor2992 is offline
    Registered User
    Join Date
    11-23-2009
    Location
    home
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: vba coding... IF, Then, Goto "multi workbooks"

    didnt work... still does nothing when i click the button... this is what my code looks like now

    Code:

    Code:
    Private Sub CommandButton1_Click()
  Dim wb As Workbook
    Set wb = Workbooks("Bookings")

If ComboBox1 = "Old Trafford Tour(Manchester)" Then
 Application.Goto wb.Workheets("Trip 1").Range("A1")
ElseIf ComboBox1 = "Sister Act(London)" Then
 Application.Goto wb.Workheets("Trip 2").Range("A1")
ElseIf ComboBox1 = "The Lion King(London)" Then
 Application.Goto wb.Workheets("Trip 3").Range("A1")
ElseIf ComboBox1 = "Christmas Shopping(London)" Then
 Application.Goto wb.Workheets("Trip 4").Range("A1")
ElseIf ComboBox1 = "Edinburgh Festival(Edinburgh)" Then
 Application.Goto wb.Workheets("Trip 5").Range("A1")
ElseIf ComboBox1 = "The Gadget Show Live(Birmingham)" Then
 Application.Goto wb.Workheets("Trip 6").Range("A1")
ElseIf ComboBox1 = "Disneyland Paris(Paris)" Then
 Application.Goto wb.Workheets("Trip 7").Range("A1")
ElseIf ComboBox1 = "Port Aventura(Barcelona)" Then
 Application.Goto wb.Workheets("Trip 8").Range("A1")
ElseIf ComboBox1 = "Booze Crooze(Dover)" Then
 Application.Goto wb.Workheets("Trip 9").Range("A1")
ElseIf ComboBox1 = "Pleasurewood Hills(Great Yasrmouth)" Then
 Application.Goto wb.Workheets("Trip 10").Range("A1")
ElseIf ComboBox1 = "Amsterdam School Trip(Amsterdam)" Then
 Application.Goto wb.Workheets("Trip 11").Range("A1")
ElseIf ComboBox1 = "Olympic Village School Trip(London)" Then
 Application.Goto wb.Workheets("Trip 12").Range("A1")
ElseIf ComboBox1 = "Celebrity Dancing On Ice(Nottingham)" Then
 Application.Goto wb.Workheets("Trip 13").Range("A1")
ElseIf ComboBox1 = "X Factor Tour(Cardiff)" Then
 Application.Goto wb.Workheets("Trip 14").Range("A1")
ElseIf ComboBox1 = "Alton Towers Halloween Weekend" Then
 Application.Goto wb.Workheets("Trip 15").Range("A1")
End If
End Sub
    sorry if thats not in the code box thing, i dont know how to put it in
    Last edited by davesexcel; 11-24-2009 at 08:16 AM. Reason: You cannot use quick reply if you are submitting code, because the code icon "#" is not in quick reply
    Reply With Quote Reply With Quote
  6. 11-24-2009, 08:24 AM #6
    davesexcel
    davesexcel is offline
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Cochrane,Alberta
    MS-Off Ver
    XL 2003,2007
    Posts
    6,259

    Re: vba coding... IF, Then, Goto "multi workbooks"

    You can Attach the two workbooks, so we can get this to work.

    I am surprised that you have been able to make a UserForm and work with VBA Code, yet you are unable to read the Forum Rules and figure out how to wrap VBA code in your posts.
    Dave

    Reply With Quote Reply With Quote
  7. 11-24-2009, 09:56 AM #7
    Domintor2992
    Domintor2992 is offline
    Registered User
    Join Date
    11-23-2009
    Location
    home
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: vba coding... IF, Then, Goto "multi workbooks"

    hey ive just read the rules for the forum, didnt realise it would be so strict but never mind its helpful so i will follow them...

    and according to excel the 2 workbooks are already linked.
    Reply With Quote Reply With Quote
  8. 11-24-2009, 08:09 PM #8
    davesexcel
    davesexcel is offline
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Cochrane,Alberta
    MS-Off Ver
    XL 2003,2007
    Posts
    6,259

    Re: vba coding... IF, Then, Goto "multi workbooks"

    Quote Originally Posted by Domintor2992 View Post
    hey ive just read the rules for the forum, didnt realise it would be so strict but never mind its helpful so i will follow them...

    and according to excel the 2 workbooks are already linked.
    I meant to attach the two workbooks to your post, so somebody can see what the problem is.
    Dave

    Reply With Quote Reply With Quote
  9. 11-25-2009, 05:05 AM #9
    Domintor2992
    Domintor2992 is offline
    Registered User
    Join Date
    11-23-2009
    Location
    home
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: vba coding... IF, Then, Goto "multi workbooks"

    Bookings.xls
    Invoice Workbook.xls

    there we go
    Reply With Quote Reply With Quote
  10. 11-25-2009, 07:33 AM #10
    davesexcel
    davesexcel is offline
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Cochrane,Alberta
    MS-Off Ver
    XL 2003,2007
    Posts
    6,259

    Re: vba coding... IF, Then, Goto "multi workbooks"

    Code:
    If ComboBox1 = "Old Trafford Tour(Manchester)" Then
 Application.Goto wb.Workheets("Trip 1").Range("A1")
    This is in the combo box Old Trafford Guided Tour (Manchester) They don't match,

    Most of what is in the code and the ComboBox do not match.

    Another issue could be that Worksheets is spelled wrong in every line
    Dave

    Reply With Quote Reply With Quote
  11. 11-25-2009, 10:45 AM #11
    Domintor2992
    Domintor2992 is offline
    Registered User
    Join Date
    11-23-2009
    Location
    home
    MS-Off Ver
    Excel 2007
    Posts
    6

    Talking Re: vba coding... IF, Then, Goto "multi workbooks"

    Solved.. thank you for your help... the last thing you suggested made it work.

    all working perfect now

    Cheers
    Dom
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.2.0