Worksheet changes!

[antman123]

I have made a userform that updates information in another worksheet to the main sheet that the user sees. Everytime you click the update button(on the userform), the excel screen switchs to the other worksheet. Is there a way where the details still get updated, but the screen doesnt move to the other worksheet?
Thanks

 

[Oaktree]

What's the code behind the userform? Do you have any select code that references another sheet? (such as Sheets("Sheet2").range("A1").select )

 

[antman123]

Here is the code behind the "Update" button of the userform...

When this is clicked, excel switches to the worksheet "user info" to show the changes made.

Set tableRange = ThisWorkbook.Worksheets("User Info").Range("C1:K93")
personname = ThisWorkbook.Worksheets("User Info").Range("M2").Value
Worksheets("User Info").Activate


If (Not (IsNumeric(DeskNo))) Then
MsgBox prompt:="Error - Please enter a valid Desk Number", Buttons:=vbCritical

ElseIf (Not (IsNumeric(ExtNo))) Then
MsgBox prompt:="Error - Please enter a valid Ext Number", Buttons:=vbCritical

ElseIf (Not (IsNumeric(TelID))) Then
MsgBox prompt:="Error - Please enter a valid Telephone Number", Buttons:=vbCritical

Else

Columns("C:C").Select
Selection.Find(What:=Range("M2"), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, 0).Value = Name1
ActiveCell.Offset(0, 1).Value = CInt(DeskNo)
ActiveCell.Offset(0, 2).Value = CInt(ExtNo)
ActiveCell.Offset(0, 3).Value = TelID
ActiveCell.Offset(0, 4).Value = Serial
ActiveCell.Offset(0, 5).Value = ID
ActiveCell.Offset(0, 6).Value = GID
MsgBox ("Details Updated")


End If

 

[Oaktree]

Try this:

application.screenupdating = false

Set tableRange = ThisWorkbook.Worksheets("User Info").Range("C1:K93")
personname = ThisWorkbook.Worksheets("User Info").Range("M2").Value
Worksheets("User Info").Activate


If (Not (IsNumeric(DeskNo))) Then
MsgBox prompt:="Error - Please enter a valid Desk Number", Buttons:=vbCritical

ElseIf (Not (IsNumeric(ExtNo))) Then
MsgBox prompt:="Error - Please enter a valid Ext Number", Buttons:=vbCritical

ElseIf (Not (IsNumeric(TelID))) Then
MsgBox prompt:="Error - Please enter a valid Telephone Number", Buttons:=vbCritical

Else

Columns("C:C").Select
Selection.Find(What:=Range("M2"), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, 0).Value = Name1
ActiveCell.Offset(0, 1).Value = CInt(DeskNo)
ActiveCell.Offset(0, 2).Value = CInt(ExtNo)
ActiveCell.Offset(0, 3).Value = TelID
ActiveCell.Offset(0, 4).Value = Serial
ActiveCell.Offset(0, 5).Value = ID
ActiveCell.Offset(0, 6).Value = GID
MsgBox ("Details Updated")

Worksheets("OTHER SHEET").Activate
application.screenupdating = true


End If

 

[antman123]

Perfect!!! Thank you VERY much.

 

[Oaktree]

I suppose you should really put the

Worksheets("OTHER SHEET").Activate
application.screenupdating = true

outside of the end if, but you can play with that to get the desired results.