Mixing
VBA Userform Selected Listbox item copy and delete
0
I have this code which that:
- UserForm1 Listbox 1 populates it's item from Worksheet1
- UserForm2 contains textboxes and a submit button
- When I select an item from UserForm1 ListBox1, it copies the values to UserForm2 Textboxes
- When I go into UserForm 2 and click submit button, I would want the row from Worksheet1 moved across to Worksheet2
Below is the code within UserForm1 ListBox1
Private Sub UserForm1ListBox1_Click()
With UserForm2
.TextBox1 = ListBox1.Column(0)
.TextBox2 = ListBox1.Column(1)
.TextBox3 = ListBox1.Column(2)
.TextBox4 = ListBox1.Column(3)
.TextBox5 = ListBox1.Column(4)
End With
End Sub
Below is the code within UserForm2 submit button but getting error commented in the code.
Private Sub Userform2SubmitButton_Click()
Dim i As Long
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
Worksheets("Worksheet1").Range("A" & i + 1). _
Copy Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(0)
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = Worksheets("Worksheet1").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Worksheet1").Range("A" & LastRow).Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate '<------------------error here!
Worksheets("Worksheet1").Rows(ActiveCell.Row).Delete
End If
End If
Next i
End Sub
After hours and days of searching I still cant get this resolved. Hoping anyone could help me. Thank you
excel vba excel-vba listbox userform
edited Jan 7 at 8:47
Pᴇʜ
24.9k63052
asked Jan 2 at 20:28
KevKev
417
add a comment |
0
I have this code which that:
- UserForm1 Listbox 1 populates it's item from Worksheet1
- UserForm2 contains textboxes and a submit button
- When I select an item from UserForm1 ListBox1, it copies the values to UserForm2 Textboxes
- When I go into UserForm 2 and click submit button, I would want the row from Worksheet1 moved across to Worksheet2
Below is the code within UserForm1 ListBox1
Private Sub UserForm1ListBox1_Click()
With UserForm2
.TextBox1 = ListBox1.Column(0)
.TextBox2 = ListBox1.Column(1)
.TextBox3 = ListBox1.Column(2)
.TextBox4 = ListBox1.Column(3)
.TextBox5 = ListBox1.Column(4)
End With
End Sub
Below is the code within UserForm2 submit button but getting error commented in the code.
Private Sub Userform2SubmitButton_Click()
Dim i As Long
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
Worksheets("Worksheet1").Range("A" & i + 1). _
Copy Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(0)
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = Worksheets("Worksheet1").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Worksheet1").Range("A" & LastRow).Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate '<------------------error here!
Worksheets("Worksheet1").Rows(ActiveCell.Row).Delete
End If
End If
Next i
End Sub
After hours and days of searching I still cant get this resolved. Hoping anyone could help me. Thank you
excel vba excel-vba listbox userform
edited Jan 7 at 8:47
Pᴇʜ
24.9k63052
asked Jan 2 at 20:28
KevKev
417
You are looking for your search item in a single cell and presumably not finding it, hence the error. You can't activate a cell that doesn't exist.
– SJR
Jan 2 at 20:39
@SJR thank you. How do I correct this. My worksheet1 does have data in there
– Kev
Jan 2 at 20:48
You can add a line to avoid the error, but what confuses me is why you are only looking in a single cellRange("A" & LastRow). Were you meaning to search a larger range?
– SJR
Jan 2 at 20:52
@SJR yeah that is what I was hoping for. Is to search the entire worksheet and select the item from the listbox
– Kev
Jan 2 at 20:56
add a comment |
0
0
0
I have this code which that:
- UserForm1 Listbox 1 populates it's item from Worksheet1
- UserForm2 contains textboxes and a submit button
- When I select an item from UserForm1 ListBox1, it copies the values to UserForm2 Textboxes
- When I go into UserForm 2 and click submit button, I would want the row from Worksheet1 moved across to Worksheet2
Below is the code within UserForm1 ListBox1
Private Sub UserForm1ListBox1_Click()
With UserForm2
.TextBox1 = ListBox1.Column(0)
.TextBox2 = ListBox1.Column(1)
.TextBox3 = ListBox1.Column(2)
.TextBox4 = ListBox1.Column(3)
.TextBox5 = ListBox1.Column(4)
End With
End Sub
Below is the code within UserForm2 submit button but getting error commented in the code.
Private Sub Userform2SubmitButton_Click()
Dim i As Long
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
Worksheets("Worksheet1").Range("A" & i + 1). _
Copy Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(0)
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = Worksheets("Worksheet1").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Worksheet1").Range("A" & LastRow).Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate '<------------------error here!
Worksheets("Worksheet1").Rows(ActiveCell.Row).Delete
End If
End If
Next i
End Sub
After hours and days of searching I still cant get this resolved. Hoping anyone could help me. Thank you
excel vba excel-vba listbox userform
edited Jan 7 at 8:47
Pᴇʜ
24.9k63052
asked Jan 2 at 20:28
KevKev
417
I have this code which that:
- UserForm1 Listbox 1 populates it's item from Worksheet1
- UserForm2 contains textboxes and a submit button
- When I select an item from UserForm1 ListBox1, it copies the values to UserForm2 Textboxes
- When I go into UserForm 2 and click submit button, I would want the row from Worksheet1 moved across to Worksheet2
Below is the code within UserForm1 ListBox1
Private Sub UserForm1ListBox1_Click()
With UserForm2
.TextBox1 = ListBox1.Column(0)
.TextBox2 = ListBox1.Column(1)
.TextBox3 = ListBox1.Column(2)
.TextBox4 = ListBox1.Column(3)
.TextBox5 = ListBox1.Column(4)
End With
End Sub
Below is the code within UserForm2 submit button but getting error commented in the code.
Private Sub Userform2SubmitButton_Click()
Dim i As Long
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
Worksheets("Worksheet1").Range("A" & i + 1). _
Copy Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(0)
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = Worksheets("Worksheet1").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Worksheet1").Range("A" & LastRow).Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate '<------------------error here!
Worksheets("Worksheet1").Rows(ActiveCell.Row).Delete
End If
End If
Next i
End Sub
After hours and days of searching I still cant get this resolved. Hoping anyone could help me. Thank you
excel vba excel-vba listbox userform
excel vba excel-vba listbox userform
edited Jan 7 at 8:47
Pᴇʜ
24.9k63052
asked Jan 2 at 20:28
KevKev
417
edited Jan 7 at 8:47
Pᴇʜ
24.9k63052
asked Jan 2 at 20:28
KevKev
417
edited Jan 7 at 8:47
Pᴇʜ
24.9k63052
edited Jan 7 at 8:47
Pᴇʜ
24.9k63052
edited Jan 7 at 8:47
Pᴇʜ
24.9k63052
24.9k63052
asked Jan 2 at 20:28
KevKev
417
asked Jan 2 at 20:28
KevKev
417
asked Jan 2 at 20:28
KevKev
417
417
You are looking for your search item in a single cell and presumably not finding it, hence the error. You can't activate a cell that doesn't exist.
– SJR
Jan 2 at 20:39
@SJR thank you. How do I correct this. My worksheet1 does have data in there
– Kev
Jan 2 at 20:48
You can add a line to avoid the error, but what confuses me is why you are only looking in a single cellRange("A" & LastRow). Were you meaning to search a larger range?
– SJR
Jan 2 at 20:52
@SJR yeah that is what I was hoping for. Is to search the entire worksheet and select the item from the listbox
– Kev
Jan 2 at 20:56
add a comment |
You are looking for your search item in a single cell and presumably not finding it, hence the error. You can't activate a cell that doesn't exist.
– SJR
Jan 2 at 20:39
@SJR thank you. How do I correct this. My worksheet1 does have data in there
– Kev
Jan 2 at 20:48
You can add a line to avoid the error, but what confuses me is why you are only looking in a single cellRange("A" & LastRow). Were you meaning to search a larger range?
– SJR
Jan 2 at 20:52
@SJR yeah that is what I was hoping for. Is to search the entire worksheet and select the item from the listbox
– Kev
Jan 2 at 20:56
You are looking for your search item in a single cell and presumably not finding it, hence the error. You can't activate a cell that doesn't exist.
– SJR
Jan 2 at 20:39
You are looking for your search item in a single cell and presumably not finding it, hence the error. You can't activate a cell that doesn't exist.
– SJR
Jan 2 at 20:39
@SJR thank you. How do I correct this. My worksheet1 does have data in there
– Kev
Jan 2 at 20:48
@SJR thank you. How do I correct this. My worksheet1 does have data in there
– Kev
Jan 2 at 20:48
You can add a line to avoid the error, but what confuses me is why you are only looking in a single cell
Range("A" & LastRow). Were you meaning to search a larger range?– SJR
Jan 2 at 20:52
You can add a line to avoid the error, but what confuses me is why you are only looking in a single cell
Range("A" & LastRow). Were you meaning to search a larger range?– SJR
Jan 2 at 20:52
@SJR yeah that is what I was hoping for. Is to search the entire worksheet and select the item from the listbox
– Kev
Jan 2 at 20:56
@SJR yeah that is what I was hoping for. Is to search the entire worksheet and select the item from the listbox
– Kev
Jan 2 at 20:56
add a comment |
2 Answers
2
active
oldest
votes
0
Something along these lines. Have left a few comments/queries in the code as not sure about a few things. Note how to handle the possibility of Find not finding anything.
Private Sub Userform2SubmitButton_Click()
Dim i As Long, r As Range, ws As Worksheet
Set ws = Worksheets("Worksheet1")
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
ws.Range("A" & i + 1).Copy _
Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) 'changed offset to 1 so as not to overwrite
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row 'not sure what this is for
Set r = ws.Cells.Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not r Is Nothing Then 'avoid error if nothing found
r.EntireRow.Delete
End If
End If
End If
Next i
End Sub
answered Jan 2 at 20:59
SJRSJR
13.4k31219
I tried the code it does delete the row from worksheet1 but nothing is shown in worksheet2
– Kev
Jan 2 at 21:18
Nothing I changed has any effect on copying anything. All I did was change the offset.
– SJR
Jan 2 at 21:20
hmmm strange that there is nothing showing in sheet2 after selected row in sheet1 is deleted
– Kev
Jan 2 at 21:26
The copying is done before the deletion. Is there a stage missing? What exactly should be copied? Step through the code to check what's going on.
– SJR
Jan 2 at 21:27
stepping through keeps taking me back to the userform2
– Kev
Jan 2 at 21:49
|
show 3 more comments
0
I have managed to find out what was missing. All this time is was just
Sheets("Worksheet1").Select
I have added this to my original code for anyone who wants to use this as well. Thank you SJR for your help as well.
Private Sub Userform2SubmitButton_Click()
Dim i As Long
Sheets("Worksheet1").Select
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
Worksheets("Worksheet1").Range("A" & i + 1). _
Copy Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(0)
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = Worksheets("Worksheet1").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Worksheet1").Range("A" & LastRow).Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate '<------------------error here!
Worksheets("Worksheet1").Rows(ActiveCell.Row).Delete
End If
End If
Next i
End Sub
answered Jan 3 at 18:39
KevKev
417
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54012739%2fvba-userform-selected-listbox-item-copy-and-delete%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Something along these lines. Have left a few comments/queries in the code as not sure about a few things. Note how to handle the possibility of Find not finding anything.
Private Sub Userform2SubmitButton_Click()
Dim i As Long, r As Range, ws As Worksheet
Set ws = Worksheets("Worksheet1")
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
ws.Range("A" & i + 1).Copy _
Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) 'changed offset to 1 so as not to overwrite
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row 'not sure what this is for
Set r = ws.Cells.Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not r Is Nothing Then 'avoid error if nothing found
r.EntireRow.Delete
End If
End If
End If
Next i
End Sub
answered Jan 2 at 20:59
SJRSJR
13.4k31219
I tried the code it does delete the row from worksheet1 but nothing is shown in worksheet2
– Kev
Jan 2 at 21:18
Nothing I changed has any effect on copying anything. All I did was change the offset.
– SJR
Jan 2 at 21:20
hmmm strange that there is nothing showing in sheet2 after selected row in sheet1 is deleted
– Kev
Jan 2 at 21:26
The copying is done before the deletion. Is there a stage missing? What exactly should be copied? Step through the code to check what's going on.
– SJR
Jan 2 at 21:27
stepping through keeps taking me back to the userform2
– Kev
Jan 2 at 21:49
|
show 3 more comments
0
Something along these lines. Have left a few comments/queries in the code as not sure about a few things. Note how to handle the possibility of Find not finding anything.
Private Sub Userform2SubmitButton_Click()
Dim i As Long, r As Range, ws As Worksheet
Set ws = Worksheets("Worksheet1")
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
ws.Range("A" & i + 1).Copy _
Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) 'changed offset to 1 so as not to overwrite
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row 'not sure what this is for
Set r = ws.Cells.Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not r Is Nothing Then 'avoid error if nothing found
r.EntireRow.Delete
End If
End If
End If
Next i
End Sub
answered Jan 2 at 20:59
SJRSJR
13.4k31219
I tried the code it does delete the row from worksheet1 but nothing is shown in worksheet2
– Kev
Jan 2 at 21:18
Nothing I changed has any effect on copying anything. All I did was change the offset.
– SJR
Jan 2 at 21:20
hmmm strange that there is nothing showing in sheet2 after selected row in sheet1 is deleted
– Kev
Jan 2 at 21:26
The copying is done before the deletion. Is there a stage missing? What exactly should be copied? Step through the code to check what's going on.
– SJR
Jan 2 at 21:27
stepping through keeps taking me back to the userform2
– Kev
Jan 2 at 21:49
|
show 3 more comments
0
0
0
Something along these lines. Have left a few comments/queries in the code as not sure about a few things. Note how to handle the possibility of Find not finding anything.
Private Sub Userform2SubmitButton_Click()
Dim i As Long, r As Range, ws As Worksheet
Set ws = Worksheets("Worksheet1")
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
ws.Range("A" & i + 1).Copy _
Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) 'changed offset to 1 so as not to overwrite
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row 'not sure what this is for
Set r = ws.Cells.Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not r Is Nothing Then 'avoid error if nothing found
r.EntireRow.Delete
End If
End If
End If
Next i
End Sub
answered Jan 2 at 20:59
SJRSJR
13.4k31219
Something along these lines. Have left a few comments/queries in the code as not sure about a few things. Note how to handle the possibility of Find not finding anything.
Private Sub Userform2SubmitButton_Click()
Dim i As Long, r As Range, ws As Worksheet
Set ws = Worksheets("Worksheet1")
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
ws.Range("A" & i + 1).Copy _
Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) 'changed offset to 1 so as not to overwrite
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row 'not sure what this is for
Set r = ws.Cells.Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not r Is Nothing Then 'avoid error if nothing found
r.EntireRow.Delete
End If
End If
End If
Next i
End Sub
answered Jan 2 at 20:59
SJRSJR
13.4k31219
answered Jan 2 at 20:59
SJRSJR
13.4k31219
answered Jan 2 at 20:59
SJRSJR
13.4k31219
answered Jan 2 at 20:59
SJRSJR
13.4k31219
13.4k31219
I tried the code it does delete the row from worksheet1 but nothing is shown in worksheet2
– Kev
Jan 2 at 21:18
Nothing I changed has any effect on copying anything. All I did was change the offset.
– SJR
Jan 2 at 21:20
hmmm strange that there is nothing showing in sheet2 after selected row in sheet1 is deleted
– Kev
Jan 2 at 21:26
The copying is done before the deletion. Is there a stage missing? What exactly should be copied? Step through the code to check what's going on.
– SJR
Jan 2 at 21:27
stepping through keeps taking me back to the userform2
– Kev
Jan 2 at 21:49
|
show 3 more comments
I tried the code it does delete the row from worksheet1 but nothing is shown in worksheet2
– Kev
Jan 2 at 21:18
Nothing I changed has any effect on copying anything. All I did was change the offset.
– SJR
Jan 2 at 21:20
hmmm strange that there is nothing showing in sheet2 after selected row in sheet1 is deleted
– Kev
Jan 2 at 21:26
The copying is done before the deletion. Is there a stage missing? What exactly should be copied? Step through the code to check what's going on.
– SJR
Jan 2 at 21:27
stepping through keeps taking me back to the userform2
– Kev
Jan 2 at 21:49
I tried the code it does delete the row from worksheet1 but nothing is shown in worksheet2
– Kev
Jan 2 at 21:18
I tried the code it does delete the row from worksheet1 but nothing is shown in worksheet2
– Kev
Jan 2 at 21:18
Nothing I changed has any effect on copying anything. All I did was change the offset.
– SJR
Jan 2 at 21:20
Nothing I changed has any effect on copying anything. All I did was change the offset.
– SJR
Jan 2 at 21:20
hmmm strange that there is nothing showing in sheet2 after selected row in sheet1 is deleted
– Kev
Jan 2 at 21:26
hmmm strange that there is nothing showing in sheet2 after selected row in sheet1 is deleted
– Kev
Jan 2 at 21:26
The copying is done before the deletion. Is there a stage missing? What exactly should be copied? Step through the code to check what's going on.
– SJR
Jan 2 at 21:27
The copying is done before the deletion. Is there a stage missing? What exactly should be copied? Step through the code to check what's going on.
– SJR
Jan 2 at 21:27
stepping through keeps taking me back to the userform2
– Kev
Jan 2 at 21:49
stepping through keeps taking me back to the userform2
– Kev
Jan 2 at 21:49
|
show 3 more comments
0
I have managed to find out what was missing. All this time is was just
Sheets("Worksheet1").Select
I have added this to my original code for anyone who wants to use this as well. Thank you SJR for your help as well.
Private Sub Userform2SubmitButton_Click()
Dim i As Long
Sheets("Worksheet1").Select
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
Worksheets("Worksheet1").Range("A" & i + 1). _
Copy Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(0)
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = Worksheets("Worksheet1").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Worksheet1").Range("A" & LastRow).Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate '<------------------error here!
Worksheets("Worksheet1").Rows(ActiveCell.Row).Delete
End If
End If
Next i
End Sub
answered Jan 3 at 18:39
KevKev
417
add a comment |
0
I have managed to find out what was missing. All this time is was just
Sheets("Worksheet1").Select
I have added this to my original code for anyone who wants to use this as well. Thank you SJR for your help as well.
Private Sub Userform2SubmitButton_Click()
Dim i As Long
Sheets("Worksheet1").Select
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
Worksheets("Worksheet1").Range("A" & i + 1). _
Copy Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(0)
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = Worksheets("Worksheet1").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Worksheet1").Range("A" & LastRow).Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate '<------------------error here!
Worksheets("Worksheet1").Rows(ActiveCell.Row).Delete
End If
End If
Next i
End Sub
answered Jan 3 at 18:39
KevKev
417
add a comment |
0
0
0
I have managed to find out what was missing. All this time is was just
Sheets("Worksheet1").Select
I have added this to my original code for anyone who wants to use this as well. Thank you SJR for your help as well.
Private Sub Userform2SubmitButton_Click()
Dim i As Long
Sheets("Worksheet1").Select
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
Worksheets("Worksheet1").Range("A" & i + 1). _
Copy Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(0)
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = Worksheets("Worksheet1").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Worksheet1").Range("A" & LastRow).Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate '<------------------error here!
Worksheets("Worksheet1").Rows(ActiveCell.Row).Delete
End If
End If
Next i
End Sub
answered Jan 3 at 18:39
KevKev
417
I have managed to find out what was missing. All this time is was just
Sheets("Worksheet1").Select
I have added this to my original code for anyone who wants to use this as well. Thank you SJR for your help as well.
Private Sub Userform2SubmitButton_Click()
Dim i As Long
Sheets("Worksheet1").Select
For i = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox1.Selected(i) Then
Worksheets("Worksheet1").Range("A" & i + 1). _
Copy Worksheets("Worksheet2").Range("A" & Rows.Count).End(xlUp).Offset(0)
If UserForm1.ListBox1.ListIndex >= 0 Then
LastRow = Worksheets("Worksheet1").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Worksheet1").Range("A" & LastRow).Find(what:=UserForm1.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate '<------------------error here!
Worksheets("Worksheet1").Rows(ActiveCell.Row).Delete
End If
End If
Next i
End Sub
answered Jan 3 at 18:39
KevKev
417
answered Jan 3 at 18:39
KevKev
417
answered Jan 3 at 18:39
KevKev
417
answered Jan 3 at 18:39
KevKev
417
417
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54012739%2fvba-userform-selected-listbox-item-copy-and-delete%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You are looking for your search item in a single cell and presumably not finding it, hence the error. You can't activate a cell that doesn't exist.
– SJR
Jan 2 at 20:39
@SJR thank you. How do I correct this. My worksheet1 does have data in there
– Kev
Jan 2 at 20:48
You can add a line to avoid the error, but what confuses me is why you are only looking in a single cell
Range("A" & LastRow). Were you meaning to search a larger range?– SJR
Jan 2 at 20:52
@SJR yeah that is what I was hoping for. Is to search the entire worksheet and select the item from the listbox
– Kev
Jan 2 at 20:56