Thanks Stuart - now working perfectly - until the next problem
Thanks once again
BJ
On Feb 16, 7:16 am, Stuart Redmann <dertop...@web.de> wrote:
> > On Feb 15, BJthebear 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.
>
> > > Sub InputNewUser()
>
> > > Dim Newuser As String
> > > Dim nextRow As Long
>
> [snipped some code]
>
>
>
> > > '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
>
> This line will probably cause problems. Note that the Range function
> is invoked on the ActiveWorksheet if you don't specify a worksheet.
> You should transform these lines in such a way that you get rid of any
> Active calls (those Activate calls are a giveaway that the original
> code was recorded by the macro recorder ;-)
>
> The replacement code should look like this:
> dim CurrentCell as Excel.Range
> set CurrentCell = SrcSht.Cells(1,1)
>
> ' Search the list of the new user name: This assumes that
> ' there are no empty rows in the list of users.
> While CurrentCell <> "" and CurrentCell <> Newuser
> Set CurrentCell = CurrentCell.Offset (1,0)
> Wend
>
> if CurrentCell = NewUser then
> Msgbox "The user is already present in the list!"
> exit sub
> end if
>
> > > 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
>
> Regards,
> Stuart
--
----------------------------------------------------------------------------------
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
