Unique problem with recorded macro
 

I seem to have a "unique" problem. I have researched many differen
sites about VBA and macros, etc. ... but, none have seemed to run int
this situation.

In short, I recorded a macro to do the following: (1) select a name
region, (2) unprotect the worksheet, (3) sort the named region (b
last name, then first name), (4) protect the worksheet.

Then, I turn right around and run the macro--it does steps (1) and (2
just fine, but pauses on step (3) [Note: sometimes it does (3) the
stops, and other times it doesn't even do step (3)]

I have looked at the VBA code, and it was the same code that worke
with earlier versions of my workbook--believe me, I have re-recorde
this same macro many times--the sort works when I record it, but alway
pauses (usually before) it does the sort. [In fact, the data sor
ALWAYS works when I do it in Excel, but has problems with the VB
code.]

Even when I use the Debug toolbar to step through the code--i
highlights each step in yellow, and then runs it--but when it gets t
the sort step, the debugger will not go beyond the sort step. In fact
it will attempt running the sort (not always succeeding) and put th
cursor before the sort statement.

Needless to say, I'm frustrated! Any help will be appreciated!

Here is the VBA code, in case it helps:
Sub sort_name()
Application.Goto Reference:="student.sort"
ActiveSheet.Unprotect
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending
Key2:=Range("B4") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1
MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
DataOption2:= _
xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True
Scenarios:=True _
, AllowSorting:=True
Range("A1").Select
End Su

--
Junio
-----------------------------------------------------------------------
Junior's Profile: http://www.msusenet.com/member.php?userid=515
View this thread: http://www.msusenet.com/t-187106295

Unique problem with recorded macro
 

Example from VBA Sort Help
Sub SortRange1()
Worksheets("Sheet1").Range("A1:C20").Sort _
Key1:=Worksheets("Sheet1").Range("A1"), _
Key2:=Worksheets("Sheet1").Range("B1")
End Sub

You may need to specify the sheet.range not just range, VBA can point
anywhere and Macros tend to point at one sheet so you need to be more
specific in VBA

"Junior" wrote:


I seem to have a "unique" problem. I have researched many different
sites about VBA and macros, etc. ... but, none have seemed to run into
this situation.

In short, I recorded a macro to do the following: (1) select a named
region, (2) unprotect the worksheet, (3) sort the named region (by
last name, then first name), (4) protect the worksheet.

Then, I turn right around and run the macro--it does steps (1) and (2)
just fine, but pauses on step (3) [Note: sometimes it does (3) then
stops, and other times it doesn't even do step (3)]

I have looked at the VBA code, and it was the same code that worked
with earlier versions of my workbook--believe me, I have re-recorded
this same macro many times--the sort works when I record it, but always
pauses (usually before) it does the sort. [In fact, the data sort
ALWAYS works when I do it in Excel, but has problems with the VBA
code.]

Even when I use the Debug toolbar to step through the code--it
highlights each step in yellow, and then runs it--but when it gets to
the sort step, the debugger will not go beyond the sort step. In fact,
it will attempt running the sort (not always succeeding) and put the
cursor before the sort statement.

Needless to say, I'm frustrated! Any help will be appreciated!

Here is the VBA code, in case it helps:
Sub sort_name()
Application.Goto Reference:="student.sort"
ActiveSheet.Unprotect
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending,
Key2:=Range("B4") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True
Range("A1").Select
End Sub


--
Junior
------------------------------------------------------------------------
Junior's Profile: http://www.msusenet.com/member.php?userid=5154
View this thread: http://www.msusenet.com/t-1871062956

Unique problem with recorded macro
 

I appreciate the suggestion, and tried it, but to no avail. It stil
hangs up at the same place. Besides, the name is *defined* to b
"=Data!$A$8:$AX$57" (it includes the worksheet name).

As a further note, I have tried drastic(?) measures such as:
(1) "Help Detect and Repair..." Which took awhile, but didn'
change things.
(2) Testing VBA: Creating a smaller file, without the extra workshee
pages and formatting, and trying a similar macro and running it. I
works fine!

Speaking of which, could it be "Conditional formatting" that coul
affect the sort? I have one condition in the "Conditional formatting
that accesses a custom VBA function--but, it works completely ok whe
data is sorted in Excel itself.

Still wondering what I can do! Any other suggestions

--
Junio
-----------------------------------------------------------------------
Junior's Profile: http://www.msusenet.com/member.php?userid=515
View this thread: http://www.msusenet.com/t-187106295