1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Microsoft Excel 2003 Question (Connecting Work-Sheets)

Discussion in 'Business Applications' started by BarraGT, Sep 29, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. BarraGT

    BarraGT Thread Starter

    Joined:
    Jun 20, 2005
    Messages:
    298
    I have a spread-sheet done in Microsoft Excel 2003, but I was wondering if it was possible to do something a bit clever with it.

    What I have in the spread-sheet is two work-sheets (the tabs down the bottom of the screen). Now, one of these work-sheets is a list of DVD's that I want to buy, and the other is a list of DVD's that I already have.

    What I want to do is to have something on the work-sheet of DVD's that I want to buy where I can check them off, or press a button, or something, when I buy them, and then it removes itself from that list and adds it to the bottom of the second work-sheet (with the DVD's that I already have).

    I hope that makes sense! Any help would be appreciated.
     
  2. Glaswegian

    Glaswegian Malware Specialist

    Joined:
    Dec 5, 2004
    Messages:
    3,823
    Hi

    See if this works for you. I've named the sheets based on your post, so change them to suit the actual sheet names. I've also assumed that the lists are both in column A in both sheets - change that as well if required. This uses an input box - type in the DVD title and it will cut it from your first list and paste it to the bottom of your second list. You can use the Forms toolbar to draw a button and assign the macro. The code goes in a standard module:
    Paste Code to a New Module

    1. Press Alt+F11 from Excel to open the VB Editor.
    2. Click on the Project(Workbook) name in the left pane.
    3. Click on ‘Insert’ on the Menu Bar
    4. Select ‘Module’ from the list. The new Module will open.
    5. Paste the code into the right pane of the Module.
    Code:
    Sub MoveDVDs()
    Dim myRng As Range
    Dim Obj As Range
    Dim myReply
    
    Set myRng = Sheets("To Buy").Range("A1", Range("A65536").End(xlUp))
    
    myReply = Application.InputBox("Name your DVD", "DVD Selection")
        If myReply = False Then Exit Sub
    With myRng
        Set Obj = .Find(what:=myReply, LookIn:=xlValues)
            If Not Obj Is Nothing Then
                Obj.Cut Destination:=Sheets("Have Bought").Range("A65536").End(xlUp)
                    Else
                        MsgBox "Title not found!"
            End If
    End With
    End Sub
    
    HTH

    Regards
     
  3. BarraGT

    BarraGT Thread Starter

    Joined:
    Jun 20, 2005
    Messages:
    298
    Thankyou for your help with this!

    I think I did what you described above, but now when I click on the button that I placed in the document, it says "Compile Error: Expected End Sub", at which point it highlights the "Sub Button1_Click()" in the Microsoft Visual Basic editor.
     
  4. Glaswegian

    Glaswegian Malware Specialist

    Joined:
    Dec 5, 2004
    Messages:
    3,823
    Hi BarraGT

    Sounds like you've created your button from the Control Toolbox. This automatically creates start and end sub lines ready for code. There are a couple of options here. Open Control Toolbox and press the 'Design Mode' icon (blue triangle). Now right click on your button and then select 'View Code'. This will open the VB Editor and take you to the lines of code that begin "Sub Button1_Click()". You can copy and paste the code I posted - without the "Sub MoveDVDs()" and "End Sub" lines - after Sub Button1_Click() - now go back to Excel (Alt+F11) and exit 'Design Mode'. The code will now run when you click your button.

    The other alternative is to delete your button, open up the Forms toolbar, draw a button on your sheet and assign the macro I posted. (When you draw the button, an "Assign Macro" dialog box will open automatically).

    Let me know if this helps.

    Regards
     
  5. BarraGT

    BarraGT Thread Starter

    Joined:
    Jun 20, 2005
    Messages:
    298
    I hate to be a pain, but I'm lost on a lot of the details about macros and other things in Microsoft Excel 2003. I'm not overly familiar with it. Would you be able to describe what it is I have to do, from the start, in more simple terms?
     
  6. Glaswegian

    Glaswegian Malware Specialist

    Joined:
    Dec 5, 2004
    Messages:
    3,823
    Hi BarraGT

    OK – no problems. Let’s start at the beginning. Open Excel and your DVD spreadsheet.

    Delete any existing buttons you may have created. Now press Alt+F11 (that is, hold down the Alt key and then press the F11 key). The Visual Basic Editor (VBE) will now open. This should be split into 2 panes, similar to Windows Explorer. If you only see one pane, click on View > Project Explorer and a new pane will appear. In the left hand pane you should see a list of open workbooks. Look for the name VBAProject(My DVD File.xls) (where ‘My DVD File.xls is the name of your file)in the left hand pane. Click once to highlight the file then go to the Menu Bar and click on Insert > Module. A blank pane will now appear on the right side. Copy and paste in the code I posted earlier – remember to include the sub name and the End Sub lines. Now press Alt+F11 to return to the Excel interface.

    In Excel, right click on the space to the right of your Menu Bar. From the drop down menu click on Forms. The Forms Toolbar will now appear. Click on the ‘Button’ icon and then click and drag on the sheet to draw a button. Immediately, a dialog box called ‘Assign Macro’ will appear. Look for the dropdown called ‘Macros In’ at the bottom of the dialog. Click on the dropdown and choose ‘This Workbook’. The name of the macro you’ve just copied should appear in the list. Click on the macro and click on OK. You’ve now assigned the macro to the button. When you click on the button the macro will run.

    Let me know if this works for you.

    Regards
     
  7. BarraGT

    BarraGT Thread Starter

    Joined:
    Jun 20, 2005
    Messages:
    298
    Thankyou for those instructions, I got it working!

    There is just one small problem. When the pop-up box comes up and I enter the movie name, it does move the title from one work-sheet to the other, but it is replacing the last title that was moved. It's not adding them on, it's replacing the last entered title.

    Also, is there a way, by any chance, to be able to click the movie title for it to move from one work-sheet to the other, rather than press the button and have to type in the movie title?
     
  8. Glaswegian

    Glaswegian Malware Specialist

    Joined:
    Dec 5, 2004
    Messages:
    3,823
    Hi

    No problem. On the sheet where the to-buy titles are, right click the sheet tab and select 'View Code'. Now copy and paste in this code

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    Target.Cut Destination:=Sheets("sheet2").Range("A65536").End(xlUp).Offset(1, 0)
    End Sub
    Now when you click once on a title it will move to the second sheet. Remember to change the sheet names as required. If your list is not in column A change the line
    Code:
    If Target.Column <> 1 Then Exit Sub
    so that the number represents the correct column.

    BTW apologies - previously forgot to add in the offset parameter so that the title would move to the last blank cell - sorted it this time.

    Regards
     
  9. BarraGT

    BarraGT Thread Starter

    Joined:
    Jun 20, 2005
    Messages:
    298
    That's great! Works beautifully! Thankyou very much for all of your help!
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/403096

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice