Worksheet changes!

antman123

Board Regular
Joined
Jan 24, 2005
Messages
72
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 :biggrin:
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
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

Board Regular
Joined
Jan 24, 2005
Messages
72
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
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
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
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.
 

Forum statistics

Threads
1,148,525
Messages
5,747,188
Members
424,068
Latest member
Salim khamis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top