I had posted this question with a different header and no response, so I
thought I'd try it again. I have Sheet1 & Sheet2 that store customer
profiles in Sheet "SAVE". In Sheet "SAVE", column A is the date it was
saved, B contains either a C or F (from Sheet1 only) or a L (from Sheet2
only), Column C contains the customer name. Columns D through AJ are used to
store data form Sheet1 & 2, columns AK through AP only get populated from
Sheet2. Storing the data works great, All 36/42 columns have proper data in
them. This list will grow very long and I need to be able to use a UserForm
with a ListBox and 3 commands to 1) Recall that customer profile and input
each piece of data in their row (ex. A2:AJ2 for Sheet1 or A2:AP2 for Sheet2)
back to the proper sheet based on the qualifier in Column B. So, I envision
a listbox with a concatenated list of Customer Name & Date that I can scroll
down. When I see the customer profile I want to recall I click on it, then I
click a button called "LOAD" which then looks that Column B in that customer
row in Sheet "SAVE" and if it's C or F it goes to Sheet1 (all 36 columns) if
it's L it goes to Sheet2 (all 42 columns). The cells the 42 things go into
are all over too, it's not a solid row or a solid column in Sheet1 or 2, it's
like: H6, E6, E7,E18,B22 and so on, I got it to pull the data ok, it's the
reverse that I am having problems with. The other 2 buttons would be
"DELETE", i would highlight the name in the listbox, click DELETE, another
window would popup "Are you sure you want to delete this customer? Yes/No,
if yes deletes enter row corresponding to the customer, if no, returns to
UserForm. The 3rd button would be "CANCEL", which I have programmed. Please
help! Thank you so much!

Mike

Ok, I got the UserForm to come up with the list of data I want. I can scroll
down through all the names, Right now I have it 3 columns wide, from
Sheet"SAVE" A,B,C. When I select one, or the one that is highlighted in
blue, I need to add the 2 commands, one is "LOAD" which will import the
selected names entire row over to Sheet1 or Sheet2 depending on what's in
column B, if it contains C or F then Sheet1 and the 36 cells, if L then
Sheet2 and the 42 cells. The other button I want to add is the "DELETE"
button to delete the highlighted names record, that is, the entire row
corresponding to that name in Sheet"SAVE". I would like a confirmation on
the delete "Are you sure you want to permanently delete Mr. Smith from your
database? Yes/no as described in original post. Any help is appreciated.
Thank you!

Mike

"mikeolson" wrote:

I had posted this question with a different header and no response, so I
thought I'd try it again. I have Sheet1 & Sheet2 that store customer
profiles in Sheet "SAVE". In Sheet "SAVE", column A is the date it was
saved, B contains either a C or F (from Sheet1 only) or a L (from Sheet2
only), Column C contains the customer name. Columns D through AJ are used to
store data form Sheet1 & 2, columns AK through AP only get populated from
Sheet2. Storing the data works great, All 36/42 columns have proper data in
them. This list will grow very long and I need to be able to use a UserForm
with a ListBox and 3 commands to 1) Recall that customer profile and input
each piece of data in their row (ex. A2:AJ2 for Sheet1 or A2:AP2 for Sheet2)
back to the proper sheet based on the qualifier in Column B. So, I envision
a listbox with a concatenated list of Customer Name & Date that I can scroll
down. When I see the customer profile I want to recall I click on it, then I
click a button called "LOAD" which then looks that Column B in that customer
row in Sheet "SAVE" and if it's C or F it goes to Sheet1 (all 36 columns) if
it's L it goes to Sheet2 (all 42 columns). The cells the 42 things go into
are all over too, it's not a solid row or a solid column in Sheet1 or 2, it's
like: H6, E6, E7,E18,B22 and so on, I got it to pull the data ok, it's the
reverse that I am having problems with. The other 2 buttons would be
"DELETE", i would highlight the name in the listbox, click DELETE, another
window would popup "Are you sure you want to delete this customer? Yes/No,
if yes deletes enter row corresponding to the customer, if no, returns to
UserForm. The 3rd button would be "CANCEL", which I have programmed. Please
help! Thank you so much!

Mike

Dim Reply As String
Reply = MsgBox("Are you sure you want to delete this customer?" &
Chr(10), vbYesNo)
If Reply = vbYes Then
Do Something
End If


Regards,

Alan



"mikeolson" wrote in message
...
I had posted this question with a different header and no response, so I
thought I'd try it again. I have Sheet1 & Sheet2 that store customer
profiles in Sheet "SAVE". In Sheet "SAVE", column A is the date it was
saved, B contains either a C or F (from Sheet1 only) or a L (from Sheet2
only), Column C contains the customer name. Columns D through AJ are used
to
store data form Sheet1 & 2, columns AK through AP only get populated from
Sheet2. Storing the data works great, All 36/42 columns have proper data
in
them. This list will grow very long and I need to be able to use a
UserForm
with a ListBox and 3 commands to 1) Recall that customer profile and
input
each piece of data in their row (ex. A2:AJ2 for Sheet1 or A2:AP2 for
Sheet2)
back to the proper sheet based on the qualifier in Column B. So, I
envision
a listbox with a concatenated list of Customer Name & Date that I can
scroll
down. When I see the customer profile I want to recall I click on it,
then I
click a button called "LOAD" which then looks that Column B in that
customer
row in Sheet "SAVE" and if it's C or F it goes to Sheet1 (all 36 columns)
if
it's L it goes to Sheet2 (all 42 columns). The cells the 42 things go
into
are all over too, it's not a solid row or a solid column in Sheet1 or 2,
it's
like: H6, E6, E7,E18,B22 and so on, I got it to pull the data ok, it's
the
reverse that I am having problems with. The other 2 buttons would be
"DELETE", i would highlight the name in the listbox, click DELETE, another
window would popup "Are you sure you want to delete this customer?
Yes/No,
if yes deletes enter row corresponding to the customer, if no, returns to
UserForm. The 3rd button would be "CANCEL", which I have programmed.
Please
help! Thank you so much!

Mike

Thank you Alan, unfortunately I am stuck prior to that command. I have the
box appear with my contact list in it, columns A,B,C. I can scroll up &
down, select one, but that's where I am stuck. When I select one and click
my delete button I get an error and cannot move beyond it. Once I get the
delete button fixed I need to code the LOAD button. Here's the code I am
using for the delete button:
'The button is placed in Sheet1

Private Sub Form_Button_Delete_Click()

Dim wb As Workbook
Dim ws As Worksheet

Dim RecNo As Integer
Dim Records As Integer
Dim i As Long

Set wb = ThisWorkbook
Set ws = Worksheets("SAVE") ' This is where my records are stored A2:AP2
and down

RecNo = ListBox1.Selected ' ListBox1 is where my 3 columns of data show
up A,B,C

ws.Activate
ws.Rows(RecNo).Select
ws.Rows(RecNo).Delete

i = RecNo
While ActiveCell.Value < Empty

ws.Cells(i, 2).Select
i = i + 1

Wend

ListBox1.ListItems.Remove (RecNo - 2)

For Records = RecNo To (i - 1) Step 1

ws.Cells(Records, 2).Select
ActiveCell.Value = Records - 2
ListBox1.ListItems.Item(Records - 2) = Records - 2

Next Records

ws.Cells(1, 1).Select

Set ws = Nothing
Set wb = Nothing
End Sub

Where am I going wrong? It doesn't like the SELECTEDITEM OR SELECTED?

Mike



"Alan" wrote:


Dim Reply As String
Reply = MsgBox("Are you sure you want to delete this customer?" &
Chr(10), vbYesNo)
If Reply = vbYes Then
Do Something
End If


Regards,

Alan



"mikeolson" wrote in message
...
I had posted this question with a different header and no response, so I
thought I'd try it again. I have Sheet1 & Sheet2 that store customer
profiles in Sheet "SAVE". In Sheet "SAVE", column A is the date it was
saved, B contains either a C or F (from Sheet1 only) or a L (from Sheet2
only), Column C contains the customer name. Columns D through AJ are used
to
store data form Sheet1 & 2, columns AK through AP only get populated from
Sheet2. Storing the data works great, All 36/42 columns have proper data
in
them. This list will grow very long and I need to be able to use a
UserForm
with a ListBox and 3 commands to 1) Recall that customer profile and
input
each piece of data in their row (ex. A2:AJ2 for Sheet1 or A2:AP2 for
Sheet2)
back to the proper sheet based on the qualifier in Column B. So, I
envision
a listbox with a concatenated list of Customer Name & Date that I can
scroll
down. When I see the customer profile I want to recall I click on it,
then I
click a button called "LOAD" which then looks that Column B in that
customer
row in Sheet "SAVE" and if it's C or F it goes to Sheet1 (all 36 columns)
if
it's L it goes to Sheet2 (all 42 columns). The cells the 42 things go
into
are all over too, it's not a solid row or a solid column in Sheet1 or 2,
it's
like: H6, E6, E7,E18,B22 and so on, I got it to pull the data ok, it's
the
reverse that I am having problems with. The other 2 buttons would be
"DELETE", i would highlight the name in the listbox, click DELETE, another
window would popup "Are you sure you want to delete this customer?
Yes/No,
if yes deletes enter row corresponding to the customer, if no, returns to
UserForm. The 3rd button would be "CANCEL", which I have programmed.
Please
help! Thank you so much!

Mike

From some other posts I found this, but it is deleting the wrong row.
'
Private Sub Form_Button_Delete_Click()
Dim rng As Range, s As String
Dim idex As Long
If ListBox1.ListIndex = -1 Then Exit Sub
Set rng = Range(ListBox1.RowSource)
s = rng.Address(0, 0, xlA1, True)
idex = ListBox1.ListIndex
ListBox1.RowSource = ""
rng(idex).EntireRow.Delete
Set rng = Range(s)
Set rng = rng.Resize(rng.Rows.Count - 1)
ListBox1.RowSource = rng.Address(0, 0, xlA1, True)
End Sub
'
Here is the code for my ListBox:
'
Private Sub UserForm_Initialize()

Dim wb As Workbook
Dim ws As Worksheet

Dim rSource As String

Set wb = ThisWorkbook
Set ws = wb.Sheets("SAVE")

ws.Activate
ws.Cells(2, 1).Select
Selection.End(xlDown).Select 'last cell of range
rSource = "$A$2:$c$" & LTrim(Str(ActiveCell.Row))
formRowsource.ListBox1.RowSource = "SAVE!" & rSource
ws.Cells(1, 1).Select

Set ws = Nothing
Set wb = Nothing


End Sub
'
Why is it deleting the wrong row? I think it's deleting 9 rows above the
one I want

Mike


"mikeolson" wrote:

Thank you Alan, unfortunately I am stuck prior to that command. I have the
box appear with my contact list in it, columns A,B,C. I can scroll up &
down, select one, but that's where I am stuck. When I select one and click
my delete button I get an error and cannot move beyond it. Once I get the
delete button fixed I need to code the LOAD button. Here's the code I am
using for the delete button:
'The button is placed in Sheet1

Private Sub Form_Button_Delete_Click()

Dim wb As Workbook
Dim ws As Worksheet

Dim RecNo As Integer
Dim Records As Integer
Dim i As Long

Set wb = ThisWorkbook
Set ws = Worksheets("SAVE") ' This is where my records are stored A2:AP2
and down

RecNo = ListBox1.Selected ' ListBox1 is where my 3 columns of data show
up A,B,C

ws.Activate
ws.Rows(RecNo).Select
ws.Rows(RecNo).Delete

i = RecNo
While ActiveCell.Value < Empty

ws.Cells(i, 2).Select
i = i + 1

Wend

ListBox1.ListItems.Remove (RecNo - 2)

For Records = RecNo To (i - 1) Step 1

ws.Cells(Records, 2).Select
ActiveCell.Value = Records - 2
ListBox1.ListItems.Item(Records - 2) = Records - 2

Next Records

ws.Cells(1, 1).Select

Set ws = Nothing
Set wb = Nothing
End Sub

Where am I going wrong? It doesn't like the SELECTEDITEM OR SELECTED?

Mike



"Alan" wrote:


Dim Reply As String
Reply = MsgBox("Are you sure you want to delete this customer?" &
Chr(10), vbYesNo)
If Reply = vbYes Then
Do Something
End If


Regards,

Alan



"mikeolson" wrote in message
...
I had posted this question with a different header and no response, so I
thought I'd try it again. I have Sheet1 & Sheet2 that store customer
profiles in Sheet "SAVE". In Sheet "SAVE", column A is the date it was
saved, B contains either a C or F (from Sheet1 only) or a L (from Sheet2
only), Column C contains the customer name. Columns D through AJ are used
to
store data form Sheet1 & 2, columns AK through AP only get populated from
Sheet2. Storing the data works great, All 36/42 columns have proper data
in
them. This list will grow very long and I need to be able to use a
UserForm
with a ListBox and 3 commands to 1) Recall that customer profile and
input
each piece of data in their row (ex. A2:AJ2 for Sheet1 or A2:AP2 for
Sheet2)
back to the proper sheet based on the qualifier in Column B. So, I
envision
a listbox with a concatenated list of Customer Name & Date that I can
scroll
down. When I see the customer profile I want to recall I click on it,
then I
click a button called "LOAD" which then looks that Column B in that
customer
row in Sheet "SAVE" and if it's C or F it goes to Sheet1 (all 36 columns)
if
it's L it goes to Sheet2 (all 42 columns). The cells the 42 things go
into
are all over too, it's not a solid row or a solid column in Sheet1 or 2,
it's
like: H6, E6, E7,E18,B22 and so on, I got it to pull the data ok, it's
the
reverse that I am having problems with. The other 2 buttons would be
"DELETE", i would highlight the name in the listbox, click DELETE, another
window would popup "Are you sure you want to delete this customer?
Yes/No,
if yes deletes enter row corresponding to the customer, if no, returns to
UserForm. The 3rd button would be "CANCEL", which I have programmed.
Please
help! Thank you so much!

Mike

Got it, found this code:

Private Sub Form_Button_Delete_Click()
Sheets("SAVE").Rows(ListBox1.ListIndex + 2).Delete Shift:=xlUp
End Sub



"mikeolson" wrote:

From some other posts I found this, but it is deleting the wrong row.
'
Private Sub Form_Button_Delete_Click()
Dim rng As Range, s As String
Dim idex As Long
If ListBox1.ListIndex = -1 Then Exit Sub
Set rng = Range(ListBox1.RowSource)
s = rng.Address(0, 0, xlA1, True)
idex = ListBox1.ListIndex
ListBox1.RowSource = ""
rng(idex).EntireRow.Delete
Set rng = Range(s)
Set rng = rng.Resize(rng.Rows.Count - 1)
ListBox1.RowSource = rng.Address(0, 0, xlA1, True)
End Sub
'
Here is the code for my ListBox:
'
Private Sub UserForm_Initialize()

Dim wb As Workbook
Dim ws As Worksheet

Dim rSource As String

Set wb = ThisWorkbook
Set ws = wb.Sheets("SAVE")

ws.Activate
ws.Cells(2, 1).Select
Selection.End(xlDown).Select 'last cell of range
rSource = "$A$2:$c$" & LTrim(Str(ActiveCell.Row))
formRowsource.ListBox1.RowSource = "SAVE!" & rSource
ws.Cells(1, 1).Select

Set ws = Nothing
Set wb = Nothing


End Sub
'
Why is it deleting the wrong row? I think it's deleting 9 rows above the
one I want

Mike


"mikeolson" wrote:

Thank you Alan, unfortunately I am stuck prior to that command. I have the
box appear with my contact list in it, columns A,B,C. I can scroll up &
down, select one, but that's where I am stuck. When I select one and click
my delete button I get an error and cannot move beyond it. Once I get the
delete button fixed I need to code the LOAD button. Here's the code I am
using for the delete button:
'The button is placed in Sheet1

Private Sub Form_Button_Delete_Click()

Dim wb As Workbook
Dim ws As Worksheet

Dim RecNo As Integer
Dim Records As Integer
Dim i As Long

Set wb = ThisWorkbook
Set ws = Worksheets("SAVE") ' This is where my records are stored A2:AP2
and down

RecNo = ListBox1.Selected ' ListBox1 is where my 3 columns of data show
up A,B,C

ws.Activate
ws.Rows(RecNo).Select
ws.Rows(RecNo).Delete

i = RecNo
While ActiveCell.Value < Empty

ws.Cells(i, 2).Select
i = i + 1

Wend

ListBox1.ListItems.Remove (RecNo - 2)

For Records = RecNo To (i - 1) Step 1

ws.Cells(Records, 2).Select
ActiveCell.Value = Records - 2
ListBox1.ListItems.Item(Records - 2) = Records - 2

Next Records

ws.Cells(1, 1).Select

Set ws = Nothing
Set wb = Nothing
End Sub

Where am I going wrong? It doesn't like the SELECTEDITEM OR SELECTED?

Mike



"Alan" wrote:


Dim Reply As String
Reply = MsgBox("Are you sure you want to delete this customer?" &
Chr(10), vbYesNo)
If Reply = vbYes Then
Do Something
End If


Regards,

Alan



"mikeolson" wrote in message
...
I had posted this question with a different header and no response, so I
thought I'd try it again. I have Sheet1 & Sheet2 that store customer
profiles in Sheet "SAVE". In Sheet "SAVE", column A is the date it was
saved, B contains either a C or F (from Sheet1 only) or a L (from Sheet2
only), Column C contains the customer name. Columns D through AJ are used
to
store data form Sheet1 & 2, columns AK through AP only get populated from
Sheet2. Storing the data works great, All 36/42 columns have proper data
in
them. This list will grow very long and I need to be able to use a
UserForm
with a ListBox and 3 commands to 1) Recall that customer profile and
input
each piece of data in their row (ex. A2:AJ2 for Sheet1 or A2:AP2 for
Sheet2)
back to the proper sheet based on the qualifier in Column B. So, I
envision
a listbox with a concatenated list of Customer Name & Date that I can
scroll
down. When I see the customer profile I want to recall I click on it,
then I
click a button called "LOAD" which then looks that Column B in that
customer
row in Sheet "SAVE" and if it's C or F it goes to Sheet1 (all 36 columns)
if
it's L it goes to Sheet2 (all 42 columns). The cells the 42 things go
into
are all over too, it's not a solid row or a solid column in Sheet1 or 2,
it's
like: H6, E6, E7,E18,B22 and so on, I got it to pull the data ok, it's
the
reverse that I am having problems with. The other 2 buttons would be
"DELETE", i would highlight the name in the listbox, click DELETE, another
window would popup "Are you sure you want to delete this customer?
Yes/No,
if yes deletes enter row corresponding to the customer, if no, returns to
UserForm. The 3rd button would be "CANCEL", which I have programmed.
Please
help! Thank you so much!

Mike