I have solved the cancel button exit from the routine problem part 1 as follows:-
'Check to see if cancel button is pressed go back to Menu If Newuser = false Then Sheets("Menu").Select Exit Sub End If Still cannot get anything on the other two problems Brian Scotland On Feb 15, 11:40 am, BJthebear <brian.jerm...@gmail.com> wrote: > 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