Macro in Personal.xls not working

C

CMK

Hi everyone.

I hope you can help me with this....

I have the following macro saved in my personal.xls file:

Dim Myrange As Range, r As Object
Set Myrange = ActiveSheet.Range("RowHeight")
Application.ScreenUpdating = False
For Each r In Myrange.Rows
If r.Row Mod 5 = 0 Then
r.RowHeight = 24
Else
End If
Next r
Application.ScreenUpdating = True
End Sub

It was saved to be used in "ALL OPEN WORKBOOKS"
I work with a lot of templates...and pre existing files. The macro
works great in both cases. However, if I create a new worksheet....it
doesn't work. I get a runtime error '1004'.

Can anyone tell me how I can get it to work in the new
worksheets/workbooks.

Thanx.

PS. I am using Excel 2000, on Win2000 machines in a network
environment.

Chris
 
D

Don Guillett

You might like to mofify this to suit

Sub do5()
For i = 50 To 1 Step -5
Rows(i).RowHeight = 24
Next i
End Sub
 
D

David McRitchie

Hi Chris,
For your macro to work please check to see if your personal.xls
file is open and hidden. Window (menu), Hide

Macros will be found in any open workbook that is hidden.

Unlike macros, User Defined Functions (UDF) are not made accessible by
merely hiding the workbook they are in, they require a reference
.. i.e. =personal.xls!GetFormula(B3)
or you can add your personal.xls to your references...
http://www.mvps.org/dmcritchie/excel/install.htm
 
C

CMK

Yes, the personal.xls file is open and hidden....and the macro is
available...but I get an error when I run it.


David McRitchie said:
Hi Chris,
For your macro to work please check to see if your personal.xls
file is open and hidden. Window (menu), Hide

Macros will be found in any open workbook that is hidden.

Unlike macros, User Defined Functions (UDF) are not made accessible by
merely hiding the workbook they are in, they require a reference
. i.e. =personal.xls!GetFormula(B3)
or you can add your personal.xls to your references...
http://www.mvps.org/dmcritchie/excel/install.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

CMK said:
Hi everyone.

I hope you can help me with this....

I have the following macro saved in my personal.xls file:

Dim Myrange As Range, r As Object
Set Myrange = ActiveSheet.Range("RowHeight")
Application.ScreenUpdating = False
For Each r In Myrange.Rows
If r.Row Mod 5 = 0 Then
r.RowHeight = 24
Else
End If
Next r
Application.ScreenUpdating = True
End Sub

It was saved to be used in "ALL OPEN WORKBOOKS"
I work with a lot of templates...and pre existing files. The macro
works great in both cases. However, if I create a new worksheet....it
doesn't work. I get a runtime error '1004'.

Can anyone tell me how I can get it to work in the new
worksheets/workbooks.

Thanx.

PS. I am using Excel 2000, on Win2000 machines in a network
environment.

Chris
Click to expand...
Click to expand...
 
D

Don Guillett

I just tried your macro in a regular module the the active workbook and it
worked. I then put this into my personal.xls and ran it from the active
workbook and it worked fine.

Sub SetRowHeight()
For i = ActiveSheet.Range("RowHeight").Rows.Count To 1 Step -5
Rows(i).RowHeight = 24
Next i
End Sub

--
Don Guillett
SalesAid Software
[email protected]
CMK said:
Yes, the personal.xls file is open and hidden....and the macro is
available...but I get an error when I run it.


"David McRitchie" <[email protected]> wrote in message
Hi Chris,
For your macro to work please check to see if your personal.xls
file is open and hidden. Window (menu), Hide

Macros will be found in any open workbook that is hidden.

Unlike macros, User Defined Functions (UDF) are not made accessible by
merely hiding the workbook they are in, they require a reference
. i.e. =personal.xls!GetFormula(B3)
or you can add your personal.xls to your references...
http://www.mvps.org/dmcritchie/excel/install.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi everyone.

I hope you can help me with this....

I have the following macro saved in my personal.xls file:

Dim Myrange As Range, r As Object
Set Myrange = ActiveSheet.Range("RowHeight")
Application.ScreenUpdating = False
For Each r In Myrange.Rows
If r.Row Mod 5 = 0 Then
r.RowHeight = 24
Else
End If
Next r
Application.ScreenUpdating = True
End Sub

It was saved to be used in "ALL OPEN WORKBOOKS"
I work with a lot of templates...and pre existing files. The macro
works great in both cases. However, if I create a new worksheet....it
doesn't work. I get a runtime error '1004'.

Can anyone tell me how I can get it to work in the new
worksheets/workbooks.

Thanx.

PS. I am using Excel 2000, on Win2000 machines in a network
environment.

Chris
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

For your macro to run, you need a range named "rowheight" on the activesheet.

You could either use the row numbers or you could modify your macro to remind
you that you don't have that name (yet).

Option Explicit
Sub FixRowHeights()

Dim myRange As Range
Dim r As Range
Set myRange = Nothing
On Error Resume Next
Set myRange = ActiveSheet.Range("RowHeight")
On Error GoTo 0

If myRange Is Nothing Then
MsgBox "Time to add the RowHeight Name to this sheet!"
Exit Sub
Else
Application.ScreenUpdating = False
For Each r In myRange.Rows
If r.Row Mod 5 = 0 Then
r.RowHeight = 24
Else
'do nothing
'or reset the height
r.AutoFit
End If
Next r
Application.ScreenUpdating = True
End If
End Sub


Yes, the personal.xls file is open and hidden....and the macro is
available...but I get an error when I run it.

David McRitchie said:
Hi Chris,
For your macro to work please check to see if your personal.xls
file is open and hidden. Window (menu), Hide

Macros will be found in any open workbook that is hidden.

Unlike macros, User Defined Functions (UDF) are not made accessible by
merely hiding the workbook they are in, they require a refernce
. i.e. =personal.xls!GetFormula(B3)
or you can add your personal.xls to your references...
http://www.mvps.org/dmcritchie/excel/install.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

CMK said:
Hi everyone.

I hope you can help me with this....

I have the following macro saved in my personal.xls file:

Dim Myrange As Range, r As Object
Set Myrange = ActiveSheet.Range("RowHeight")
Application.ScreenUpdating = False
For Each r In Myrange.Rows
If r.Row Mod 5 = 0 Then
r.RowHeight = 24
Else
End If
Next r
Application.ScreenUpdating = True
End Sub

It was saved to be used in "ALL OPEN WORKBOOKS"
I work with a lot of templates...and pre existing files. The macro
works great in both cases. However, if I create a new worksheet....it
doesn't work. I get a runtime error '1004'.

Can anyone tell me how I can get it to work in the new
worksheets/workbooks.

Thanx.

PS. I am using Excel 2000, on Win2000 machines in a network
environment.

Chris
Click to expand...
Click to expand...
Click to expand...
 
C

CMK

Right you are!
Thanx for your help.

Dave Peterson said:
For your macro to run, you need a range named "rowheight" on the activesheet.

You could either use the row numbers or you could modify your macro to remind
you that you don't have that name (yet).

Option Explicit
Sub FixRowHeights()

Dim myRange As Range
Dim r As Range
Set myRange = Nothing
On Error Resume Next
Set myRange = ActiveSheet.Range("RowHeight")
On Error GoTo 0

If myRange Is Nothing Then
MsgBox "Time to add the RowHeight Name to this sheet!"
Exit Sub
Else
Application.ScreenUpdating = False
For Each r In myRange.Rows
If r.Row Mod 5 = 0 Then
r.RowHeight = 24
Else
'do nothing
'or reset the height
r.AutoFit
End If
Next r
Application.ScreenUpdating = True
End If
End Sub


Yes, the personal.xls file is open and hidden....and the macro is
available...but I get an error when I run it.

David McRitchie said:
Hi Chris,
For your macro to work please check to see if your personal.xls
file is open and hidden. Window (menu), Hide

Macros will be found in any open workbook that is hidden.

Unlike macros, User Defined Functions (UDF) are not made accessible by
merely hiding the workbook they are in, they require a refernce
. i.e. =personal.xls!GetFormula(B3)
or you can add your personal.xls to your references...
http://www.mvps.org/dmcritchie/excel/install.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi everyone.

I hope you can help me with this....

I have the following macro saved in my personal.xls file:

Dim Myrange As Range, r As Object
Set Myrange = ActiveSheet.Range("RowHeight")
Application.ScreenUpdating = False
For Each r In Myrange.Rows
If r.Row Mod 5 = 0 Then
r.RowHeight = 24
Else
End If
Next r
Application.ScreenUpdating = True
End Sub

It was saved to be used in "ALL OPEN WORKBOOKS"
I work with a lot of templates...and pre existing files. The macro
works great in both cases. However, if I create a new worksheet....it
doesn't work. I get a runtime error '1004'.

Can anyone tell me how I can get it to work in the new
worksheets/workbooks.

Thanx.

PS. I am using Excel 2000, on Win2000 machines in a network
environment.

Chris
Click to expand...
Click to expand...
Click to expand...
 
Top