PDA

View Full Version : [SOLVED] User input to delete value in named range:



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

patel
03-08-2014, 07:20 AM
Private Sub CommandButton21_Click()
Dim strName As String
Dim Result
Dim newEntry As Variant, rws As Long, adr As String
Unload Me
strName = InputBox(Prompt:="Delete Staff Member", _
Title:="DELETE 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 Result = 0 Then
MsgBox "The member of staff you have entered does not exist"
Exit Sub
Else
Range("Staff").Cells(Result, 1).Delete
MsgBox "Member deleted"
End If
On Error GoTo 0

End If

End Sub

Bob Phillips
03-08-2014, 08:34 AM
This seems tremendously convoluted to me, why not just use a dynamic named range?

patel
03-08-2014, 12:43 PM
can you show us how create dinamic range and code changes ?

Bob Phillips
03-08-2014, 05:44 PM
You create a named range that is dynamic in the number of rows and columns, that way if data is deleted, the range flexes accordingly.

For instance, say you have a range that starts in A1, and finishes in M20. Using an OFFSET dynamic named range, you would define it as

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

and that would point at A1:M20. Delete row 5 for instance, and it would automatically adjust to point at A1:M19.

mykal66
03-08-2014, 10:49 PM
Patel - This is great, thanks you very much. I've tested and it works perfectly

mykal66
03-08-2014, 10:56 PM
Hi again XLD. Because of issues in the past with colleagues deleting formulas or values i use hidden sheet a lot of the time so use this method to allow the users to amend. This way they have to think about what they are doing to add or delete and cant' delete anything in error. I will however give your method a go as well and thank you for offering a different method which i will try too - love this stuff and never get bored playing with new ways of doing things. Thank you again

patel
03-09-2014, 02:07 AM
@xld
thanks for suggestion, can the range have numbers and texts ?

Bob Phillips
03-09-2014, 06:45 AM
Yes, COUNTA counts numbers and non-numbers.