Msg Box Question

[ryan_law2000]

Here is what I want to do

When you click on "REMOVE" found in cell A4 I would like a MSG Box to appear saying
"Please Enter Shipping Out Information"
"New Ticket #" then a box to fill in
"Location" then a box to fill in
"DATE" then box to fill in

then at the bottom, a "Cancel" and "Transfer Item"

When this info is filled out and the transfer button is pressed I would like it to cut the full row then transfer that line to a different sheet, pasting that line on the next available line on that sheet and also adding "NEW TICK #", "Location" and "Date" to the new sheet... These New Colums will already have the titles and the colums to paste the info.

Any ideas out there on how to do this? It sounds like i will need to create a userform but as for the transfering and cuting the line im really lost

 

[ryan_law2000]

Ok I would Like to simplify this a bit

I have made my form now and all i need are some codes to assign

Under "Ticket #" ________ When they fill this cell with information how can I get it to automatically fill out the next Availble Cell in Colum C on SHEET 2

I can then figure out the other 2 cells my self.

As for the "Transfer" Button
I need this when pressed to cut that line picked, then delete that line, then paste it into SHEET 2 under the next available line.

The Cancel Button I just need to close the User Form

 

[xenou]

I guess you either

1)
Use a button to hide the form (DONE or OK) and return execution to a standard module that can execute your instructions based on the information in the form

2)
Use a button that basically does the same thing but is not a DONE button but more like a command button ("UPDATE MY SHEET")

3)
Create an event trigger (after_update on the textbox, something like that)

-----------------------------------------------------------------------

Typically, assuming you don't use the event trigger # 3 above which is maybe too quick on the draw, you would:

--Hide the form
--get the value from the textbox (directly or assigned to a variable)
--run your code as needed

For example, including copying to the next cell on sheet2:

Dim myForm as Userform1
Dim ws as Worksheet
Dim LRow as Long
Dim strTicketNumber as String

set myForm = new UserForm1
myForm.Show
'~do stuff on form

strTicketNumber = myForm.txtTicketNumber.value
Set ws = WorkSheets("Sheet2")

LRow = ws.Cells(Rows.Count, 3).End(xlUp).Row
ws.Cells(LRow + 1, 3).value = strTicketNumber

Unload myForm
set myForm = Nothing

I guess something like that. Does this help?

AB

Edit: Your button on the form will probably have this command:

Me.Hide

Which will return code execution to the standard module.

 

[Smitty]

I need this when pressed to cut that line picked, then delete that line, then paste it into SHEET 2 under the next available line.

What row is going to be cut, from which sheet and to which sheet?

This will take care of transferring the TextBox values:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 3<br>            Sheets("Sheet2").Cells(Rows.Count, i + 2).End(xlUp).Offset(1) = Me.Controls("TextBox" & i).Value<br>        <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click()<br>    Unload Me<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

You can use UserForm1.Show in a general sub and call it from a Forms button to load the form or use a Change event if you're using Data Validation for the "Remove" selection.

Hopefully that's a start,

 

[ryan_law2000]

the row that will be transferred depends on what hyperlink they press
I plan on having a "REMOVE" Hyperlink that will open this userform on each row (probably about 200 lines)
So if "remove" is selected on row 26 then that is the row i want to move.
if "remove" is selected on row 12 i would like that row to be transferred.
The row range will be from colum A-M

This brings up a second question:

I want the same user form to be used on each row, but how can i make it so it references the row that is selected?

 

[ryan_law2000]

So here is the code I have so far


Private Sub CommandButton1_Click()
Dim i As Long
For i = 1 To 4
Sheets("Sheet 2").Cells(Rows.Count, i + 2).End(xlUp).Offset(1) = Me.Controls("TextBox" & i).Value
Next i
Unload Me
End Sub



This Code works perfectly for transfering in cells info to the other sheet
All I need now is:

When the button is pressed i Need the row selected (The "remove" Hyperlink corosponding to that row) to be selcted from colum A to M, CUT, then row deleted, then paste into SHEET 2 on the next avaible row... which should be corrosponding to the additional information pasted from the user form

 

[ryan_law2000]

Another question,

When the Transfer button is pressed, I need ALL the cells to be filled out.
Can an alert pop-up if not all cells are filled in saying "ALL Cells must be filled in"

 

[Smitty]

For the Cut portion you can use a similar methodology to the first code I posted:

Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "M")).Cut Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    ActiveCell.EntireRow.Delete

For the last question, you need cells A-M in the active cell's row to be filled?

 

[ryan_law2000]

This Code
Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "M")).Cut Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)
ActiveCell.EntireRow.Delete

The only problem im having is it is cuting more then one row, its taking the row i want and the title row above it. I only want it to cut then delete the active row i select the hyperlink on.

I notice that when I select the hyperlink and the userform shows, the cell highlight always moves to the cell above (which is the title row)

any ideas, sorry for all the questions here im just new at userfroms

 

[Smitty]

The only problem im having is it is cuting more then one row, its taking the row i want and the title row above it. I only want it to cut then delete the active row i select the hyperlink on.

I don't see how it's doing that as it's only working with the activecell.row.

[quoteI notice that when I select the hyperlink and the userform shows, the cell highlight always moves to the cell above (which is the title row)[/quote]

That may have something to do with it, but without more detail I have no way of knowing.