ListBox with header row, delete row, protect header row

M

mikeolson

I have a listbox populated with a customer list (3 columns of data in the
listbox), 39 columns in the SAVE worksheet. I can add new customers to the
list, I can load their profile and I can delete them all ok. The problem is,
if I do not select a customer in the listbox (none highlighted blue) and
select the DELETE button, it deletes the header row (A1:AO1) in sheet SAVE.
I need to make it where it cannot delete this row. I don't want other users
to accidentally delete the header row Here is my entire code for all the
functions in the listbox:

'
Private Sub Form_Button_Close_Click()
Unload Me
End Sub

Private Sub Form_Button_Delete_Click()
Dim Reply As String
Reply = MsgBox("Are you sure you want to delete this customer?" &
Chr(10), vbYesNo)
If Reply = vbYes Then
Sheets("SAVE").Rows(ListBox1.ListIndex + 2).Delete Shift:=xlUp
End If
End Sub

Private Sub Form_Button_Load_Click()
Dim SourceRange As Excel.Range
Dim Val1 As String, Val2 As String, Val3 As String, Val4 As String, Val5
As String, Val6 As String, Val7 As String, Val8 As String, Val9 As String,
Val10 As String, Val11 As String, Val12 As String, Val13 As String, Val14 As
String, Val15 As String, Val16 As String, Val17 As String, Val18 As String,
Val19 As String, Val20 As String, Val21 As String, Val22 As String, Val23 As
String, Val24 As String, Val25 As String, Val26 As String, Val27 As String,
Val28 As String, Val29 As String, Val30 As String, Val31 As String, Val32 As
String, Val33 As String, Val34 As String, Val35 As String, Val36 As String,
Val37 As String, Val38 As String, Val39 As String
If (ListBox1.RowSource <> vbNullString) Then
'Get Range that the ListBox is bound to
Set SourceRange = Range(ListBox1.RowSource)
Else
'Get first data row
Set SourceRange = Range("SAVE!A2:AO2")
Exit Sub
End If

Val1 = ListBox1.Value
Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value
Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1, 1).Value
Val4 = SourceRange.Offset(ListBox1.ListIndex, 3).Resize(1, 1).Value
Val5 = SourceRange.Offset(ListBox1.ListIndex, 4).Resize(1, 1).Value
Val6 = SourceRange.Offset(ListBox1.ListIndex, 5).Resize(1, 1).Value
Val7 = SourceRange.Offset(ListBox1.ListIndex, 6).Resize(1, 1).Value
Val8 = SourceRange.Offset(ListBox1.ListIndex, 7).Resize(1, 1).Value
Val9 = SourceRange.Offset(ListBox1.ListIndex, 8).Resize(1, 1).Value
Val10 = SourceRange.Offset(ListBox1.ListIndex, 9).Resize(1, 1).Value
Val11 = SourceRange.Offset(ListBox1.ListIndex, 10).Resize(1, 1).Value
Val12 = SourceRange.Offset(ListBox1.ListIndex, 11).Resize(1, 1).Value
Val13 = SourceRange.Offset(ListBox1.ListIndex, 12).Resize(1, 1).Value
Val14 = SourceRange.Offset(ListBox1.ListIndex, 13).Resize(1, 1).Value
Val15 = SourceRange.Offset(ListBox1.ListIndex, 14).Resize(1, 1).Value
Val16 = SourceRange.Offset(ListBox1.ListIndex, 15).Resize(1, 1).Value
Val17 = SourceRange.Offset(ListBox1.ListIndex, 16).Resize(1, 1).Value
Val18 = SourceRange.Offset(ListBox1.ListIndex, 17).Resize(1, 1).Value
Val19 = SourceRange.Offset(ListBox1.ListIndex, 18).Resize(1, 1).Value
Val20 = SourceRange.Offset(ListBox1.ListIndex, 19).Resize(1, 1).Value
Val21 = SourceRange.Offset(ListBox1.ListIndex, 20).Resize(1, 1).Value
Val22 = SourceRange.Offset(ListBox1.ListIndex, 21).Resize(1, 1).Value
Val23 = SourceRange.Offset(ListBox1.ListIndex, 22).Resize(1, 1).Value
Val24 = SourceRange.Offset(ListBox1.ListIndex, 23).Resize(1, 1).Value
Val25 = SourceRange.Offset(ListBox1.ListIndex, 24).Resize(1, 1).Value
Val26 = SourceRange.Offset(ListBox1.ListIndex, 25).Resize(1, 1).Value
Val27 = SourceRange.Offset(ListBox1.ListIndex, 26).Resize(1, 1).Value
Val28 = SourceRange.Offset(ListBox1.ListIndex, 27).Resize(1, 1).Value
Val29 = SourceRange.Offset(ListBox1.ListIndex, 28).Resize(1, 1).Value
Val30 = SourceRange.Offset(ListBox1.ListIndex, 29).Resize(1, 1).Value
Val31 = SourceRange.Offset(ListBox1.ListIndex, 30).Resize(1, 1).Value
Val32 = SourceRange.Offset(ListBox1.ListIndex, 31).Resize(1, 1).Value
Val33 = SourceRange.Offset(ListBox1.ListIndex, 32).Resize(1, 1).Value
Val34 = SourceRange.Offset(ListBox1.ListIndex, 33).Resize(1, 1).Value
Val35 = SourceRange.Offset(ListBox1.ListIndex, 34).Resize(1, 1).Value
Val36 = SourceRange.Offset(ListBox1.ListIndex, 35).Resize(1, 1).Value
Val37 = SourceRange.Offset(ListBox1.ListIndex, 36).Resize(1, 1).Value
Val38 = SourceRange.Offset(ListBox1.ListIndex, 37).Resize(1, 1).Value
Val39 = SourceRange.Offset(ListBox1.ListIndex, 38).Resize(1, 1).Value
If Val2 = "L" Then
Sheets("SETUP").Range("AA4") = 3
Sheets("L").Range("R5") = Val3
Sheets("L").Range("R6") = Val4
Sheets("L").Range("R7") = Val5
Sheets("L").Range("E7") = Val6
Sheets("L").Range("E6") = Val7
Sheets("L").Range("H6") = Val8
Sheets("L").Range("N7") = Val9
Sheets("L").Range("N6") = Val10
Sheets("L").Range("K6") = Val11
Sheets("L").Range("E9") = Val12
Sheets("L").Range("C10") = Val13
Sheets("L").Range("E10") = Val14
Sheets("L").Range("E11") = Val15
Sheets("L").Range("E12") = Val16
Sheets("L").Range("E14") = Val17
Sheets("L").Range("E16") = Val18
Sheets("L").Range("E18") = Val19
Sheets("L").Range("E20") = Val20
Sheets("L").Range("B22") = Val21
Sheets("L").Range("B24") = Val22
Sheets("L").Range("R9") = Val23
Sheets("L").Range("R10") = Val24
Sheets("L").Range("R11") = Val25
Sheets("L").Range("R12") = Val26
Sheets("L").Range("R13") = Val27
Sheets("L").Range("R15") = Val28
Sheets("L").Range("R16") = Val29
Sheets("L").Range("R17") = Val30
Sheets("L").Range("R18") = Val31
Sheets("L").Range("R21") = Val32
Sheets("L").Range("R22") = Val33
Sheets("L").Range("R23") = Val34
Sheets("L").Range("R24") = Val35
Sheets("L").Range("Q15") = Val36
Sheets("L").Range("Q16") = Val37
Sheets("L").Range("Q17") = Val38
Sheets("L").Range("Q18") = Val39
Else
If Val2 = "F" Then
Sheets("SETUP").Range("AA4") = 1
Else
Sheets("SETUP").Range("AA4") = 2
End If
Sheets("F").Range("R5") = Val3
Sheets("F").Range("R6") = Val4
Sheets("F").Range("R7") = Val5
Sheets("F").Range("E7") = Val6
Sheets("F").Range("E6") = Val7
Sheets("F").Range("H6") = Val8
Sheets("F").Range("N7") = Val9
Sheets("F").Range("N6") = Val10
Sheets("F").Range("K6") = Val11
Sheets("F").Range("E9") = Val12
Sheets("F").Range("C10") = Val13
Sheets("F").Range("E10") = Val14
Sheets("F").Range("E11") = Val15
Sheets("F").Range("E12") = Val16
Sheets("F").Range("E14") = Val17
Sheets("F").Range("E16") = Val18
Sheets("F").Range("E18") = Val19
Sheets("F").Range("E20") = Val20
Sheets("F").Range("B22") = Val21
Sheets("F").Range("B24") = Val22
Sheets("F").Range("R9") = Val23
Sheets("F").Range("R10") = Val24
Sheets("F").Range("R11") = Val25
Sheets("F").Range("R12") = Val26
Sheets("F").Range("R13") = Val27
Sheets("F").Range("R15") = Val28
Sheets("F").Range("R16") = Val29
Sheets("F").Range("R21") = Val30
Sheets("F").Range("R22") = Val31
Sheets("F").Range("R23") = Val32
Sheets("F").Range("R24") = Val33
Sheets("F").Range("R14") = Val34
End If
If Sheets("SETUP").Range("AA4") = 1 Then
Sheets("F").Select
Sheets("F").CommandButton7.Visible = True
Sheets("F").CommandButton8.Visible = False
Else
If Sheets("SETUP").Range("AA4") = 3 Then
Sheets("L").Select
Else
If Sheets("SETUP").Range("AA4") = 2 Then
Sheets("F").Select
Sheets("F").CommandButton7.Visible = False
Sheets("F").CommandButton8.Visible = True
End If
End If
End If

'Clean Up
Set SourceRange = Nothing
Unload Me
End Sub


Private Sub Label1_Click()

End Sub

Private Sub ListBox1_Click()

End Sub

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
If Sheets("MAIN").Range("AB5") = "MAIN" Then
Sheets("MAIN").Select
Else
If Sheets("MAIN").Range("AB5") = "F" Then
Sheets("F").Select
Else
If Sheets("MAIN").Range("AB5") = "L" Then
Sheets("L").Select
Else
If Sheets("MAIN").Range("AB5") = "SETUP" Then
Sheets("SETUP").Select
End If
End If
End If
End If

End Sub
'
Thank you for your help!

Mike
 
T

Tom Ogilvy

Private Sub Form_Button_Delete_Click()
Dim Reply As String

if listbox1.ListIndex = -1 then exit sub

Reply = MsgBox("Are you sure you want to delete this customer?" &
Chr(10), vbYesNo)
If Reply = vbYes Then
Sheets("SAVE").Rows(ListBox1.ListIndex + 2).Delete Shift:=xlUp
End If
End Sub
 
M

mikeolson

Works great! Thank you!

Mike

Tom Ogilvy said:
Private Sub Form_Button_Delete_Click()
Dim Reply As String

if listbox1.ListIndex = -1 then exit sub

Reply = MsgBox("Are you sure you want to delete this customer?" &
Chr(10), vbYesNo)
If Reply = vbYes Then
Sheets("SAVE").Rows(ListBox1.ListIndex + 2).Delete Shift:=xlUp
End If
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

View Listrow contents in Labels 0
Loop returns only one value, does not loop & an assignment to columnhow-to problem. 26
Cannot select items in listbox 0
ListBox (arrow keys pick each item) 9
Eliminate blank row at top of Text Box entries 3
Make VBA ComboBox_Change work in 3 sheets 1
Need a little help with a custom function 1
Printing Next Row 5

Top