I have written a sub routine to add a name to a dynamic list but I
have three problems with it and would be grateful for some advice.
First the routine:-
Sub InputNewUser()
'
' InputNewUser Macro
' Macro recorded 01/02/2011 by Brian
'
Dim Newuser As String
Dim nextRow As Long
Dim flag As Boolean
flag = False
'find out if main database or staff copy - true if main database
false if email copy
For Each wksheet In Application.Worksheets
If wksheet.Name = "HoursWorkedexpenses" Then
flag = True
Exit For
End If
Next wksheet
If flag = False Then
MsgBox "You do not have sufficient authority to create a new
user"
End
Else
End If
'Ask for name of new user
Newuser = Application.InputBox("Please enter name of new user (Surname
first)")
'turns it into proper letters
Newuser = Application.Proper(Newuser)
'Check to see if cancel button is pressed go back to Menu
If Newuser = "" Then
Sheets("Menu").Select
Exit Sub
End If
'Routine to check if Newuser already exists in list of users
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Find last row
Set SrcSht = Sheets("UserList")
nextRow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
Application.ScreenUpdating = False
Range("A" & nextRow).Select
ActiveCell.FormulaR1C1 = Newuser
Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Application.ScreenUpdating = True
'display message to show that it has been added to userlist
MsgBox (Newuser & " has been added to List of Users")
'return to menu page
Sheets("Menu").Select
End Sub
The problems are as follows:-
1. I have just added the code to check to see if the cancel button is
pressed but it does not exit the subroutine - it adds a false to the
list of users.
2. How do I check to see if the Newuser entry already exists in the
list of users
3. The routine seems to work when running it from the VBA window but
when the macro is attached to a button on the "Menu" Wrksheet it fails
to add the name to the list of users. This routine worked up until a
few days ago and it really has me stumped
Any help would be appreciated
Brian
Scotland
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
