I built a spreadsheet with a table that updates like a database, all one click buttons. To be sure that a row (record) is not deleted above the “freeze” line, I added the code at lines 29-30 & 39-42.
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
| Sub DelRowIf()
Dim iResp As Integer
Dim CurrRow As Long
CurrRow = ActiveCell.Row
iResp = MsgBox("Do you want to delete the selected row? Careful, no 'Undo' is available.", vbYesNo)
'check To see If NO button selected
If iResp = vbNo Then
'do this code
Exit Sub
End If
If iResp = vbYes Then
'do this code
If CurrRow < 12 Then
MsgBox "A Header row was selected. Please select a row below the header and try again."
Exit Sub
Else
Selection.EntireRow.Delete
End If
End If
End Sub |
In one of my sheets I use a “Highlight” feature so the user can see witn no uncertainty exactly which cell/row is selected. The problem with it is that the Worksheet_SelectionChange event which drives the action, also prevents normal copy and paste. To disable the highlight when needed, I added a check box to toggle the code in and out of the Sheets active code.
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
| Private Sub CheckBox1_Click()
Dim VBCodeMod As CodeModule
Dim LineNum As Long
If Me.CheckBox1 = True Then
Me.CheckBox1.BackColor = "&H00C0FFFF"
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & Chr(13) & _
"On Error Resume Next" & Chr(13) & _
"Application.ScreenUpdating = False" & Chr(13) & _
"Static OldRng As Range" & Chr(13) & _
"Target.Cells.Interior.ColorIndex = 6" & Chr(13) & _
"OldRng.Cells.Interior.ColorIndex = xlColorIndexNone" & Chr(13) & _
"Set OldRng = Target" & Chr(13) & _
"Application.ScreenUpdating = True" & Chr(13) & _
"End Sub" ' You can use Target.Cells or Target.EntireRow on lines above
End With
Else
Me.CheckBox1.BackColor = "&HFFFFFF"
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.DeleteLines LineNum - 9, 9 ' see warning at end of page!!!
End With
End If
Me.Cells.Interior.ColorIndex = xlColorIndexNone
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Do not post code below this line or it may get deleted by above routine.
' Everything below this section is added and removed by the above routine.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Application.ScreenUpdating = False
Static OldRng As Range
Target.Cells.Interior.ColorIndex = 6
OldRng.Cells.Interior.ColorIndex = xlColorIndexNone
Set OldRng = Target
Application.ScreenUpdating = True
End Sub |
I spread sheet. It’s what I do, so you don’t have to.