Naming a range on not-active sheet

nord7287

New Member
Joined
Aug 22, 2005
Messages
6
Pretty simple problem (I think), but I couldn't find it anywhere on the board, so I decided to ask.

The user changes a drop down on "Top 30 Corridors", which changes some values on "Combo". Those values then are used to determine the new ranges on "OB Transactions". If I have the macro actually select the "OB Transactions" sheet, everything works fine. However, if I take that line out, then I get the application/user defined error message. It looks funny now, because when you change the dropdown, it flips you to a different sheet, and then back to the main sheet...seems like this should be an easy thing to avoid, but I don't know enough VB to troubleshoot.
Thanks for the help!

Sub rangechange()
Dim country, timeframea, timeframeb As Integer

country = Sheets("Combo").Cells(72, 3).Value
timeframea = Sheets("combo").Cells(72, 2).Value
timeframeb = Sheets("combo").Cells(72, 1).Value

x = (country - 1) * 219 + 4
y = country * 219 + 3
z = timeframea + 2
a = timeframeb + 2

Sheets("OB Transactions").Select
If country = 14 Then
Sheets("OB Transactions").Columns(z).name = "txnrange"
Sheets("OB Transactions").Columns(a).name = "oldrank"
ElseIf country < 15 Then
Sheets("OB Transactions").Range(Cells(x, z), Cells(y, z)).name = "txnrange"
Sheets("OB Transactions").Range(Cells(x, a), Cells(y, a)).name = "oldrank"
End If
Sheets("OB Transactions").Range(Cells(4, z), Cells(2849, 71)).name = "Corridor"

Sheets("Top 30 Corridors").Select
ActiveSheet.Columns("a:j").AutoFit

End Sub
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello nord7287, welcome to the board.
The quickest/easiest way to solve what (I think) you're looking for would be to add a couple of lines that turn off (and then on) the screen updating. This will still allow the code to select the OB Transactions sheet to make the changes, you (or the user) just won't see it happen.
For this you would simply add the line:
Application.ScreenUpdating = False
at the top of your code (or just somewhere before the line):
Sheets("OB Transactions").Select
and then:
Application.ScreenUpdating = True
just before the end.

Is that what you're looking for?
 

nord7287

New Member
Joined
Aug 22, 2005
Messages
6
That does the trick...I knew it was a simple solution, but didn't think of that at all. Seems to work best if I turn the screen update back on after selecting the main sheet again. Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,636
Members
412,334
Latest member
ExcelForLifeDontHate
Top