0

I currently have 3 sheets: Input, Process, Output and a macro that uses values displayed on the input sheet and various stores on the process sheet. The problem is when the user presses a submit button linked to the macro on the input page the sheet switches to the Process sheet before displaying the Output sheet. I understand that this is because of this line of code:

Worksheets("Process").Select

However whenever I remove it from the macro everything goes madly out of range. Is there any way of selecting a sheet without actually visually moving to it? I need the macro to do its thing and then simply display the output sheet. Thanks in advance!

Community
  • 1
  • 1
James Baker
  • 331
  • 3
  • 8
  • 26
  • See [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for methods on getting away from relying on select and activate to accomplish your goals. –  Jun 08 '16 at 09:55

2 Answers2

1

As @Jeeped stated and referenced, avoid using Select and Activate, in addition it is safer to qualify references.

For example you can use Range("A1").Value to get a value of the cell A1 in the currently active worksheet, but what if the user didn't have that sheet active at the time or another proc had moved the view? you could get the value of cell A1 from potentially any worksheet.

It would be best to create a reference to the worksheet and then send all your work through it, this way you do not need to change the active worksheet and there is no ambiguity about where the range values are coming from.

For example: -

Option Explicit
Dim WkSht_I As Worksheet 'Input
Dim WkSht_P As Worksheet 'Process
Dim WkSht_O As Worksheet 'Output

Public Sub Sample()
Set WkSht_I = ThisWorkbook.Worksheets("Input")
Set WkSht_P = ThisWorkbook.Worksheets("Process")
Set WkSht_O = ThisWorkbook.Worksheets("Output")

MsgBox "Input A1 = " & WkSht_I.Range("A1").Value
MsgBox "Process A1 = " & WkSht_P.Range("A1").Value
MsgBox "Output  A1 = " & WkSht_O.Range("A1").Value

Set WkSht_O = Nothing
Set WkSht_P = Nothing
Set WkSht_I = Nothing
End Sub

Converting your procedures to this method should be safer and clearer and you can set the active sheet just once for it to show content while the others or being worked on.

Gary Evans
  • 1,850
  • 4
  • 15
  • 30
0

@Gary's method is the best method to go with when you are working with multiple worksheets.

If you are working with only two sheets, (Considering you have activesheet and target sheet) I am going to recommend

With Worksheets("Process")
    Debug.Print .Range("A1")
    Debug.Print Range("A1")
End With

Notice "." infront of Range.

The "." indicates that it is part of With

In other words, .Range("A1") is same as Worksheets("Process").Range("A1")

Because second Range("A1") does not have "." it is same as Activesheet.Range("B1") even it's inside of the With-End

If the activesheet is Process Then the out put will be same

But when you select worksheet other than Process, because activesheet changed, the output will be different.

This will avoide using Select which changes the activesheet

MutjayLee
  • 578
  • 3
  • 6