| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox with header row, delete row, protect header row
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ListBox with header row, delete row, protect header row
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 -- Regards, Tom Ogilvy "mikeolson" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ListBox with header row, delete row, protect header row
Works great! Thank you!
Mike "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "mikeolson" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Add Listbox header using VBA | Excel Discussion (Misc queries) | |||
| Protect Header | Excel Discussion (Misc queries) | |||
| how to protect header & footer | Excel Programming | |||
| listbox header row | Excel Programming | |||
| Adding Header to multicolumn ListBox | Excel Programming | |||
Linear Mode
