> 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
