Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
AdvancedCommandBars
Question is why (IsEmptyWorksheet) can hang up the running of the application
does this statement need a Dim This statement produces all the system faces and takes 3 minutes to run. Dim iFaceId As Integer ' Tracks Current FaceID Dim iColumn As Integer ' Tracks Current column in worksheet Dim iRow As Integer ' Tracks current Row in worksheet Dim ctl As CommandBarControl Dim cbr As CommandBar If Not IsEmptyWorksheet(ActiveSheet) Then Exit Sub On Error GoTo Recover Application.ScreenUpdating = False Set cbr = CommandBars.Add(Position:=msoBarFloating, MenuBar:=False, temporary:=True) Set ctl = cbr.Controls.Add(Type:=msoControlButton, temporary:=True) iRow = 1 Do For iColumn = 1 To 10 iFaceId = iFaceId + 1 Application.StatusBar = "FaceID = " & iFaceId ctl.FaceId = iFaceId ctl.CopyFace ActiveSheet.Paste Cells(iRow, iColumn + 1) Cells(iRow, iColumn).Value = iFaceId Next iColumn iRow = iRow + 1 Loop Recover: If Err.Number = 1004 Then Resume Next Application.StatusBar = False cbr.Delete End Sub -- William<"M" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
AdvancedCommandBars
It doesn't need a dim statement, but you do need to have a function with that
name. Do you have a function like that that the calling sub can find? If no, maybe you can use this one: Function IsEmptyWorksheet(wks As Worksheet) As Boolean If wks.UsedRange.Address = "$A$1" _ And IsEmpty(wks.Range("A1").Value) Then IsEmptyWorksheet = True Else IsEmptyWorksheet = False End If End Function Here are some alternatives, too: Doug Glancy's: http://www.dicks-blog.com/archives/2...-viewer-addin/ John Walkenbach's: http://j-walk.com/ss/excel/tips/tip67.htm Jim Rech's: http://www.oaltd.co.uk/MVP/MVPPage.asp#JimRech Jim's version for the Office2007 button images can be found on Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm or directly from: http://www.rondebruin.nl/files/BtnImages.zip Sunday88310 wrote: Question is why (IsEmptyWorksheet) can hang up the running of the application does this statement need a Dim This statement produces all the system faces and takes 3 minutes to run. Dim iFaceId As Integer ' Tracks Current FaceID Dim iColumn As Integer ' Tracks Current column in worksheet Dim iRow As Integer ' Tracks current Row in worksheet Dim ctl As CommandBarControl Dim cbr As CommandBar If Not IsEmptyWorksheet(ActiveSheet) Then Exit Sub On Error GoTo Recover Application.ScreenUpdating = False Set cbr = CommandBars.Add(Position:=msoBarFloating, MenuBar:=False, temporary:=True) Set ctl = cbr.Controls.Add(Type:=msoControlButton, temporary:=True) iRow = 1 Do For iColumn = 1 To 10 iFaceId = iFaceId + 1 Application.StatusBar = "FaceID = " & iFaceId ctl.FaceId = iFaceId ctl.CopyFace ActiveSheet.Paste Cells(iRow, iColumn + 1) Cells(iRow, iColumn).Value = iFaceId Next iColumn iRow = iRow + 1 Loop Recover: If Err.Number = 1004 Then Resume Next Application.StatusBar = False cbr.Delete End Sub -- William<"M" -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|