Excel Command Button

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?
 
Joined
Oct 20, 2004
Messages
7,837
Hi Jack - been awhile. ;)
Have you tried doing the copying part and assigning a macro to it?
 

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.
 

Attachments

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!
 
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.
 

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).
 
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
 

Attachments

Joined
Jul 25, 2004
Messages
5,458
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.
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

Attachments

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?
 
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...
 

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?
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top