mykal66
03-08-2014, 04:02 AM
Hi
I use this piece of code to allow a user to add additional values to a named range via an input box. The range is then updated to include the additional value. I am leaving my current job soon so would like colleagues who don't know how to do this manually to be able to do the reverse as well if possible but can't figure out how to change this code to do the opposite or find anything on the internet. i.e. look for name entered into the input box, delete the name if it exists, shift names up so there are no blanks (not delete whole row) the amend the range of cells associated with the name range.
Any help really appreciated as always
Private Sub CommandButton20_Click()
Dim strName As String
Dim Result
Dim newEntry As Variant, rws As Long, adr As String
Unload Me
strName = InputBox(Prompt:="Add Staff Member", _
Title:="ADD STAFF MEMBER", Default:="Enter Staff Member Here!")
If strName = "Enter Staff Member Here!" Or _
strName = vbNullString Then
Exit Sub
Else
On Error Resume Next
Result = WorksheetFunction.Match(strName, Range("Staff"), False)
If Err = 0 Then
MsgBox "The member of staff you have entered already exists"
Else
newEntry = strName
rws = Range("Staff").Rows.Count
Range("Staff").Cells(1, 1).Offset(rws, _
0).Value = newEntry
With Range("Staff").Name
adr = .RefersTo
.Delete
End With
Range(adr).Resize(rws + 1, 1).Name = "Staff"
Err.Clear
MsgBox "New member of staff has been added to list"
End If
On Error GoTo 0
End If
End Sub
I use this piece of code to allow a user to add additional values to a named range via an input box. The range is then updated to include the additional value. I am leaving my current job soon so would like colleagues who don't know how to do this manually to be able to do the reverse as well if possible but can't figure out how to change this code to do the opposite or find anything on the internet. i.e. look for name entered into the input box, delete the name if it exists, shift names up so there are no blanks (not delete whole row) the amend the range of cells associated with the name range.
Any help really appreciated as always
Private Sub CommandButton20_Click()
Dim strName As String
Dim Result
Dim newEntry As Variant, rws As Long, adr As String
Unload Me
strName = InputBox(Prompt:="Add Staff Member", _
Title:="ADD STAFF MEMBER", Default:="Enter Staff Member Here!")
If strName = "Enter Staff Member Here!" Or _
strName = vbNullString Then
Exit Sub
Else
On Error Resume Next
Result = WorksheetFunction.Match(strName, Range("Staff"), False)
If Err = 0 Then
MsgBox "The member of staff you have entered already exists"
Else
newEntry = strName
rws = Range("Staff").Rows.Count
Range("Staff").Cells(1, 1).Offset(rws, _
0).Value = newEntry
With Range("Staff").Name
adr = .RefersTo
.Delete
End With
Range(adr).Resize(rws + 1, 1).Name = "Staff"
Err.Clear
MsgBox "New member of staff has been added to list"
End If
On Error GoTo 0
End If
End Sub