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 <[email protected]> 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 [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
