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.

Excel Command Button

Discussion in 'Business Applications' started by JackAndCoke, Nov 6, 2007.

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

    JackAndCoke Thread Starter

    Joined:
    Apr 26, 2007
    Messages:
    152
    I have a cell "M11" that contains this

    Code:
    =J11&","&E12&","&E13&","&E14&","&E15&","&E16&","&E17&","&J12&","&J13&","&J14&","&J15&","&J16&","&J17&","&C21&","&D21&","&E21&","&F21&","&I21&","&J21&","&C22&","&D22&","&E22&","&F22&","&I22&","&J22&","&C23&","&D23&","&E23&","&F23&","&I23&","&J23&","&C24&","&D24&","&E24&","&F24&","&I24&","&J24&","&C25&","&D25&","&E25&","&F25&","&I25&","&J25
    These are values of cells delimited by a comma obviously. I need a button that takes these values and pastes them in the sheet titled "Data". I would like for it to just paste the order above in each row. J11->A2, E12->B2, E13->C2, E14->D2... J25->AA2. Then I need it to advance to the next row that so that when they can fill out those cells, hit the button and it will paste it into A3, B3, C3, D3. Essentially I'm taking cells from one sheet and moving them into a "Datasheet" view to be imported into an Access database. Can anyone write the VBA code for the button? I don't know any VBA :(

    Does this make sense?
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Hi Jack - been awhile. ;)
    Have you tried doing the copying part and assigning a macro to it?
     
  3. JackAndCoke

    JackAndCoke Thread Starter

    Joined:
    Apr 26, 2007
    Messages:
    152
    Well, here's the file I'm working with so you get a better idea of the movement of data. We're going from the Business Review Sheet to the Data sheet. There are a lot of ranges involved, and if I had my way, ranges would be made illegal in excel because I don't know how to work through static ranges in vba.
     

    Attached Files:

  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I know there are people who love ranges, and I have used them some myself, but I know what you mean!
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    This would make a whole lot more sense just to have it in Access, period, with the Business Review sheet being a form. I see what you want to do - saw something similar here not too long ago...either firefytr or bomb_21 took care of that.
     
  6. JackAndCoke

    JackAndCoke Thread Starter

    Joined:
    Apr 26, 2007
    Messages:
    152
    We can't do it that way because we are dispersing it across the country and have 20+ simultaneous users. Rather than send out 20 Access databases, we just have them put it in the excel form, click the button, move it to the data sheet, then send me the sheet to load into one access database.

    My bosses crack me up sometimes with their ideas. On this project 4 chiefs, 1 Indian(Me).
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    See the attached. Note the formulas in A6:H6, you could use something similar on your "front end" sheet, in a hidden row if necessary. Then try the test macro from Sheet1. HTH

    Sub test()
    x = (Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row) + 1
    For i = 1 To 8
    Sheets("Sheet2").Cells(x, i) = Cells(6, i).Value
    Next i
    Range("A1:C4").ClearContents
    End Sub
     

    Attached Files:

  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Haven't had time to mess with it much, gotta jet to class in a minute, but I just tinkered with some of the following code for a status check on filling out the information...
    Code:
    Option Explicit
    
    Private Sub cmbSend_Click()
        Dim wsData As Worksheet, blnFinish As Boolean, blnHasData As Boolean
        Dim arrData() As Variant, i As Long, lastRow As Long
        Set wsData = ThisWorkbook.Sheets("Data")
        blnFinish = True
        For i = 1 To Me.Range("VendorInfo").Cells.Count
            If Me.Range("VendorInfo").Cells(i, 1).Value = "" Then
                blnFinish = True
                GoTo NotFinished
            End If
        Next i
        For i = 1 To Me.Range("BusinessInfo").Cells.Count
            If Me.Range("BusinessInfo").Cells(i, 1).Value = "" Then
                blnFinish = True
                GoTo NotFinished
            End If
        Next i
        blnFinish = False
        blnHasData = False
        For i = 1 To Me.Range("Item1").Cells.Count
            If Me.Range("Item1").Cells(1, i).Value = "" Then
                blnFinish = True
            Else
                blnHasData = True
            End If
        Next i
        If blnHasData = True And blnFinish = True Then GoTo NotFinished
        blnFinish = False
        blnHasData = False
        For i = 1 To Me.Range("Item2").Cells.Count
            If Me.Range("Item2").Cells(1, i).Value = "" Then
                blnFinish = True
            Else
                blnHasData = True
            End If
        Next i
        If blnHasData = True And blnFinish = True Then GoTo NotFinished
        blnFinish = False
        blnHasData = False
        For i = 1 To Me.Range("Item3").Cells.Count
            If Me.Range("Item3").Cells(1, i).Value = "" Then
                blnFinish = True
            Else
                blnHasData = True
            End If
        Next i
        If blnHasData = True And blnFinish = True Then GoTo NotFinished
        blnFinish = False
        blnHasData = False
        For i = 1 To Me.Range("Item4").Cells.Count
            If Me.Range("Item4").Cells(1, i).Value = "" Then
                blnFinish = True
            Else
                blnHasData = True
            End If
        Next
        If blnHasData = True And blnFinish = True Then GoTo NotFinished
        blnFinish = False
        blnHasData = False
        For i = 1 To Me.Range("Item5").Cells.Count
            If Me.Range("Item5").Cells(1, i).Value = "" Then
                blnFinish = True
            Else
                blnHasData = True
            End If
        Next i
        If blnHasData = True And blnFinish = True Then GoTo NotFinished
        MsgBox "Well formed data!", vbInformation, "GOOD TO GO!"
        Exit Sub
    NotFinished:
        MsgBox "Some data needs to be filled in.", vbExclamation, "ERROR!"
    End Sub
    I named a couple ranges and such.
     
  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Hmmm, Zack, so you are a jailbird too? Good cause!
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Yup! Was a jail bird today. :D The online site (Edit: My online site, in the link in my sig) will be up for another week and a half. There are only a few foundations I'll go out of my way to help, this being one of them, also the MDA and Cystic Fibrosis Foundation.


    And I just realized I didn't post a copy of the workbook! I'll do so now.
     

    Attached Files:

  11. JackAndCoke

    JackAndCoke Thread Starter

    Joined:
    Apr 26, 2007
    Messages:
    152
    My excel sheet doesn't recognize MCONCAT. I have the Analysis Toolpak and Analysis Toolpak VBA enabled. Do I need anything else?
     
  12. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Zack left a cell blank - make sure you have it filled in - it worked for me, although the data just goes to column M on the same page - J&C, were you wanting it to go to the data page? Also, each new click of the button overwrites the previous set of data...
     
  13. JackAndCoke

    JackAndCoke Thread Starter

    Joined:
    Apr 26, 2007
    Messages:
    152
    I'm getting an invalid name error. Everything is filled in and it doesn't recognize MCONCAT. Is that an Add-in that I need to get?
     
  14. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    what version of Excel do you have?
     
  15. JackAndCoke

    JackAndCoke Thread Starter

    Joined:
    Apr 26, 2007
    Messages:
    152
    2003. I think there might be a problem with how it reads =MCONCAT(C21:H21,","). I think the "," might be the problem. When I went to insert function MCONCAT and had C21:H21 in the top box it was fine but when I put "," in the second box it said !Value in red.
     
  16. 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/648598

  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