How do I perform a VBA copy / paste operation between sheets with a command button and add a message box to indicate an invalid action?

[nc_4080]

Hi,

Have just joined the forum here as it seems very helpful and looks like there are many experienced Excel users willing to help find solutions to 'problems'.....

I am trying to develop a system to allow me select different parts from multiple sheets and then add them into a bill of material on a separate sheet.

https://docs.google.com/file/d/0B1mEsDskmn73czkybU9NQXMwdmM/edit?usp=sharing

I have written some VB script the best I can and it includes a 'commandbutton' to make it easy for the user of the workbook to use.

This is how it works:

1. Go to 'Step 1 - Manifold 8640' tab and highlight one of the yellow cells.
2. Once you have selected a yellow cell, you then click the 'select' button above.
3. The value (along with other values) are copied to the 'Smart Calc' sheet.
4. Then you go to 'Step 2 - Gland Plate' tab, select a yellow cell, click the 'Select' button.
5. Notice how the 'Step 2 - Gland Plate' value is entered into a different group in the 'Smart Calc' sheet - this is great................BUT

The issue I am having is:

1. If the user goes to 'Step 1 - Manifold 8640' tab, highlights the desired yellow cell then presses the 'Select' button twice, it will add two lines of data in the relevant 'Step 1 - Manifold 8640' section....This is ok.
2. But when the user clicks a third time, the 'Step 1' data will overflow into the 'Step 2' section of the 'Smart Calc' tab...because the script tells the data to look for the next available line to write to. This is what I am trying to avoid!

Instead, i would like a message box to popup to indicate to the user that they cannot add anymore data into into the 'Step 1' or 'Step 2' sections of the 'Smart Calc' tab until they clear the data from within the relevant section in 'Smat Calc'.

I would like to set limits on where each 'Step 1' or 'Step 2' data is written to the 'Smart Calc' sheet (so it is within its relevant section). As you can see the script basically looks for the next available cell but this isn't really good when the data 'overflows'.

Note that you are able to highlight the yellow cells in the 'Smart Calc' tab and press the delete key, it will delete the data to clear the line. That is how i designed it to work so if the user makes a mistake they are able to just delete the relevant line and enter new data.

Thanks in advance to anyone that is able to help with this as i have been reading on these (and other) forums for while now but cannot find a solution!

NC_4080

 

[nc_4080]

I have posted the VBA script from the two sheets I am working on if that helps:

---From sheet 'Step 1 - Manifold 8460'---



Private Sub CommandButton1_Click()
Dim c As Variant
Dim cValue As Variant
Dim test As Variant


Application.DisplayAlerts = False
Application.EnableEvents = False


For Each c In Selection.Cells
Worksheets("Step 1 - Manifold 8640").Activate
c.Activate
test = ActiveCell.Value


Worksheets("Smart Calc").Activate
Worksheets("Smart Calc").Range("A10").Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select

Loop
ActiveCell.Value = test


Next


Application.DisplayAlerts = False
Application.EnableEvents = False


Worksheets("Step 1 - Manifold 8640").Activate
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub


---From sheet 'Step 2 - Gland Plate'---



Private Sub CommandButton1_Click()
Dim c As Variant
Dim cValue As Variant
Dim test As Variant


Application.DisplayAlerts = False
Application.EnableEvents = False


For Each c In Selection.Cells
Worksheets("Step 2 - Gland Plate").Activate
c.Activate
test = ActiveCell.Value


Worksheets("Smart Calc").Activate
Worksheets("Smart Calc").Range("A26").Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select

Loop
ActiveCell.Value = test



Next


Application.DisplayAlerts = False
Application.EnableEvents = False


Worksheets("Step 2 - Gland Plate").Activate
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub