Can this private sub code be transferred to a code without a given condition?

[TanjaU]

Hi everyone,

I am very new to VBA and I have a question concerning the following code that was written for use on an excel worksheet.

I want to cut a row from a table (tb_Datenbank) and paste it in a table on another worksheet (tb_Datenbank1) in the same workbook.
The code works fine but I have to make an input " x" in front of the row I want to copy, in order to get this code to work. Besides I have to put the code in the code window of the worksheet, as it is a private sub

My aim now is to have a button (makro) that is doing the same thing but without making an entry "x". I would just like to position my cursor in a cell within that row, push the button with the makro and have the same
result. Is this possible? Can anyone help me with the new code for this. Thanks, Tanja


Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngErste As Long
If Target.Column = 1 Then
If Target.Count = 1 Then
If UCase(Target) = "X" Then
With Worksheets("Archiv")
lngErste = IIf(IsEmpty(.Cells(.Rows.Count, 1)), _
.Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count) + 1
Rows(Target.Row).Copy
.Cells(lngErste, 1).PasteSpecial Paste:=xlValues
Rows(Target.Row).Delete shift:=xlUp
End With
End If
End If
End If
End Sub

 

[Micron]

A few ways you could approach this:
- use Before Double Click event to ascertain which cell (therefore which row or column) was double clicked, or
- use a mouse event (e.g. Before Right Click) to detect right click on a cell, or
- use the Input Box function but use the one that gets a range from the user (can be just one cell)
- select a cell and click the button, wherein that code returns the active cell address, or row/column
I think I'd opt for idea 1, 3 or 4 because of all the other things that people expect to happen with a right click on a sheet, and this isn't one of them.
I take it that based on your post, all of the IF stuff can go. What remains to be seen is what approach you want to take.

I have to put the code in the code window of the worksheet, as it is a private sub

Being private has not much to do with where the procedure is located when you get right down to it. Perhaps this link does not explain that but it's a decent place to start if you're going to learn vba.

Understanding scope and visibility (VBA)

Office VBA reference topic

learn.microsoft.com

Please post more than a couple of lines of code within code tags (use vba button on posting toolbar) to maintain indentation and readability.

 

[TanjaU]

Micron, thank you for your answer.
I will try one of the suggested methods before writing again.
And I will be aware of posting correctly with the VBA Button.