| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ScreenUpdating function not working?
I'm using Office 2003 on Win XP Pro SP2, Dell PC. I have the following vba
script in a spreadsheet, and I want to hide all the activity.. but the ScreenUpdating function doesn't seem to be hiding anything, tons of stuff still shows on screen. (Note: I just added in all the comments to this screen for readability, in my code they aren't there.) And please don't laugh that I'm using SendKeys statements, it was quick and I'm not a programmer. Thanks. ----------------------------------------- Private Sub cmdApp_Click() ' Turn off Screen Updating Application.ScreenUpdating = False ' Unprotect the worksheet Excel.SendKeys ("%(t)") Excel.SendKeys ("p") Excel.SendKeys ("p") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") ' Goto Cell A22 Excel.SendKeys ("{F5}") Excel.SendKeys ("{DELETE}") Excel.SendKeys ("A22") Excel.SendKeys ("{ENTER}") ' Select current row and next 7 rows Excel.SendKeys ("+({DOWN 7})") ' Insert a bunch of rows Excel.SendKeys ("%(i)") Excel.SendKeys ("r") ' Move cursor down 1 row Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re3.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re3.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re2.50{ENTER}") Excel.SendKeys ("{DOWN}") ' Insert another row Excel.SendKeys ("%(i)") Excel.SendKeys ("r") ' Move cursor two cells to the right Excel.SendKeys ("{RIGHT 2}") ' Select 6 columns wide (blank row that was just inserted) Excel.SendKeys ("+({RIGHT 6})") ' Format cell pattern to be solid black Excel.SendKeys ("%(o)ebp{TAB}{DOWN}{ENTER}{TAB 2}{ENTER}") ' Go down two rows Excel.SendKeys ("{DOWN 2}") ' Select 3 columns and 4 rows Excel.SendKeys ("+({RIGHT 3}{DOWN 4})") ' Copy selected rows Excel.SendKeys ("^(c)") ' Move curser up 9 rows Excel.SendKeys ("{UP 9}") ' Paste rows from previous copy then ESC out of copy sequence Excel.SendKeys ("^(v)") Excel.SendKeys ("{ESC}") ' Move right one cell, delete its contents Excel.SendKeys ("{RIGHT}") Excel.SendKeys ("{DELETE}") ' Move right two cells, delete its contents Excel.SendKeys ("{RIGHT 2}") Excel.SendKeys ("{DELETE}") ' Move down 2 and left 1 cell, delete its contents Excel.SendKeys ("{DOWN 2}{LEFT}{DELETE}") ' Move down 2 cells, delete its contents Excel.SendKeys ("{DOWN 2}{DELETE}") ' Move up 4 cells and left 1 cell Excel.SendKeys ("{UP 4}{LEFT}") ' Re-protect the worksheet Excel.SendKeys ("%(t)pp") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") ' Turn Screen Updating back on Application.ScreenUpdating = True End Sub ~ Paul |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ScreenUpdating function not working?
I've never used Sendkeys this much, but maybe it sure looks like showing those
dialogs is flashing before my eyes. I got confused over what was happening, but your code might not be too difficult to change. And it may be easier to debug. I think I have the syntax correct--but I'm not sure I have the ranges correct. If you print this and try it manually, you may be able to match up the addresses that should change: Option Explicit Private Sub cmdApp_Click() Dim iRow As Long 'Turn off Screen Updating Application.ScreenUpdating = False 'Unprotect the worksheet With ActiveSheet .Unprotect Password:="excel" 'Goto Cell A22 'Insert a bunch of rows .Range("a22").Resize(7, 1).EntireRow.Insert 'Move cursor down 1 row For iRow = 23 To 30 Step 2 .Rows(iRow).RowHeight = 12.75 .Rows(iRow + 1).RowHeight = 3.75 Next iRow .Rows(31).RowHeight = 2.5 .Rows(32).Insert .Range("A34").Offset(0, 2).Resize(1, 6).Interior.ColorIndex = 1 .Range("a34").Offset(0, 2).Resize(4, 4).Copy _ Destination:=.Range("a25") Application.CutCopyMode = False .Range("a34").Offset(0, 1).Resize(1, 2).ClearContents .Range("a34").Offset(2, 1).ClearContents .Range("a34").Offset(2, 0).ClearContents .Range("a30").Select 'Re-protect the worksheet .Protect Password:="excel" End With 'Turn Screen Updating back on Application.ScreenUpdating = True End Sub The .offset(x,y) means go up/down x number of rows and go right/left y number of columns. so .offset(0,1) means stay in the same row and go to the right 1 column. The .resize(x,y) means to make the range x rows by y columns. ======== I'd say translating the things you did into Sendkeys was pretty impressive, though. Paul J. wrote: I'm using Office 2003 on Win XP Pro SP2, Dell PC. I have the following vba script in a spreadsheet, and I want to hide all the activity.. but the ScreenUpdating function doesn't seem to be hiding anything, tons of stuff still shows on screen. (Note: I just added in all the comments to this screen for readability, in my code they aren't there.) And please don't laugh that I'm using SendKeys statements, it was quick and I'm not a programmer. Thanks. ----------------------------------------- Private Sub cmdApp_Click() ' Turn off Screen Updating Application.ScreenUpdating = False ' Unprotect the worksheet Excel.SendKeys ("%(t)") Excel.SendKeys ("p") Excel.SendKeys ("p") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") ' Goto Cell A22 Excel.SendKeys ("{F5}") Excel.SendKeys ("{DELETE}") Excel.SendKeys ("A22") Excel.SendKeys ("{ENTER}") ' Select current row and next 7 rows Excel.SendKeys ("+({DOWN 7})") ' Insert a bunch of rows Excel.SendKeys ("%(i)") Excel.SendKeys ("r") ' Move cursor down 1 row Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re3.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re3.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re2.50{ENTER}") Excel.SendKeys ("{DOWN}") ' Insert another row Excel.SendKeys ("%(i)") Excel.SendKeys ("r") ' Move cursor two cells to the right Excel.SendKeys ("{RIGHT 2}") ' Select 6 columns wide (blank row that was just inserted) Excel.SendKeys ("+({RIGHT 6})") ' Format cell pattern to be solid black Excel.SendKeys ("%(o)ebp{TAB}{DOWN}{ENTER}{TAB 2}{ENTER}") ' Go down two rows Excel.SendKeys ("{DOWN 2}") ' Select 3 columns and 4 rows Excel.SendKeys ("+({RIGHT 3}{DOWN 4})") ' Copy selected rows Excel.SendKeys ("^(c)") ' Move curser up 9 rows Excel.SendKeys ("{UP 9}") ' Paste rows from previous copy then ESC out of copy sequence Excel.SendKeys ("^(v)") Excel.SendKeys ("{ESC}") ' Move right one cell, delete its contents Excel.SendKeys ("{RIGHT}") Excel.SendKeys ("{DELETE}") ' Move right two cells, delete its contents Excel.SendKeys ("{RIGHT 2}") Excel.SendKeys ("{DELETE}") ' Move down 2 and left 1 cell, delete its contents Excel.SendKeys ("{DOWN 2}{LEFT}{DELETE}") ' Move down 2 cells, delete its contents Excel.SendKeys ("{DOWN 2}{DELETE}") ' Move up 4 cells and left 1 cell Excel.SendKeys ("{UP 4}{LEFT}") ' Re-protect the worksheet Excel.SendKeys ("%(t)pp") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") ' Turn Screen Updating back on Application.ScreenUpdating = True End Sub ~ Paul -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ScreenUpdating function not working?
Thank you, Dave. I tried your code and it didn't work exactly as my SendKeys
code, but I think it's close. I'll try tweaking a few references and see if that fixes it. If I can't get it to work the same, would you mind if I sent you the workbook to take a quick look? Thanks again, Paul ---------------------------------------------- "Dave Peterson" wrote: I've never used Sendkeys this much, but maybe it sure looks like showing those dialogs is flashing before my eyes. I got confused over what was happening, but your code might not be too difficult to change. And it may be easier to debug. I think I have the syntax correct--but I'm not sure I have the ranges correct. If you print this and try it manually, you may be able to match up the addresses that should change: Option Explicit Private Sub cmdApp_Click() Dim iRow As Long 'Turn off Screen Updating Application.ScreenUpdating = False 'Unprotect the worksheet With ActiveSheet .Unprotect Password:="excel" 'Goto Cell A22 'Insert a bunch of rows .Range("a22").Resize(7, 1).EntireRow.Insert 'Move cursor down 1 row For iRow = 23 To 30 Step 2 .Rows(iRow).RowHeight = 12.75 .Rows(iRow + 1).RowHeight = 3.75 Next iRow .Rows(31).RowHeight = 2.5 .Rows(32).Insert .Range("A34").Offset(0, 2).Resize(1, 6).Interior.ColorIndex = 1 .Range("a34").Offset(0, 2).Resize(4, 4).Copy _ Destination:=.Range("a25") Application.CutCopyMode = False .Range("a34").Offset(0, 1).Resize(1, 2).ClearContents .Range("a34").Offset(2, 1).ClearContents .Range("a34").Offset(2, 0).ClearContents .Range("a30").Select 'Re-protect the worksheet .Protect Password:="excel" End With 'Turn Screen Updating back on Application.ScreenUpdating = True End Sub The .offset(x,y) means go up/down x number of rows and go right/left y number of columns. so .offset(0,1) means stay in the same row and go to the right 1 column. The .resize(x,y) means to make the range x rows by y columns. ======== I'd say translating the things you did into Sendkeys was pretty impressive, though. Paul J. wrote: I'm using Office 2003 on Win XP Pro SP2, Dell PC. I have the following vba script in a spreadsheet, and I want to hide all the activity.. but the ScreenUpdating function doesn't seem to be hiding anything, tons of stuff still shows on screen. (Note: I just added in all the comments to this screen for readability, in my code they aren't there.) And please don't laugh that I'm using SendKeys statements, it was quick and I'm not a programmer. Thanks. ----------------------------------------- Private Sub cmdApp_Click() ' Turn off Screen Updating Application.ScreenUpdating = False ' Unprotect the worksheet Excel.SendKeys ("%(t)") Excel.SendKeys ("p") Excel.SendKeys ("p") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") ' Goto Cell A22 Excel.SendKeys ("{F5}") Excel.SendKeys ("{DELETE}") Excel.SendKeys ("A22") Excel.SendKeys ("{ENTER}") ' Select current row and next 7 rows Excel.SendKeys ("+({DOWN 7})") ' Insert a bunch of rows Excel.SendKeys ("%(i)") Excel.SendKeys ("r") ' Move cursor down 1 row Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re3.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re3.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re2.50{ENTER}") Excel.SendKeys ("{DOWN}") ' Insert another row Excel.SendKeys ("%(i)") Excel.SendKeys ("r") ' Move cursor two cells to the right Excel.SendKeys ("{RIGHT 2}") ' Select 6 columns wide (blank row that was just inserted) Excel.SendKeys ("+({RIGHT 6})") ' Format cell pattern to be solid black Excel.SendKeys ("%(o)ebp{TAB}{DOWN}{ENTER}{TAB 2}{ENTER}") ' Go down two rows Excel.SendKeys ("{DOWN 2}") ' Select 3 columns and 4 rows Excel.SendKeys ("+({RIGHT 3}{DOWN 4})") ' Copy selected rows Excel.SendKeys ("^(c)") ' Move curser up 9 rows Excel.SendKeys ("{UP 9}") ' Paste rows from previous copy then ESC out of copy sequence Excel.SendKeys ("^(v)") Excel.SendKeys ("{ESC}") ' Move right one cell, delete its contents Excel.SendKeys ("{RIGHT}") Excel.SendKeys ("{DELETE}") ' Move right two cells, delete its contents Excel.SendKeys ("{RIGHT 2}") Excel.SendKeys ("{DELETE}") ' Move down 2 and left 1 cell, delete its contents Excel.SendKeys ("{DOWN 2}{LEFT}{DELETE}") ' Move down 2 cells, delete its contents Excel.SendKeys ("{DOWN 2}{DELETE}") ' Move up 4 cells and left 1 cell Excel.SendKeys ("{UP 4}{LEFT}") ' Re-protect the worksheet Excel.SendKeys ("%(t)pp") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") ' Turn Screen Updating back on Application.ScreenUpdating = True End Sub ~ Paul -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ScreenUpdating function not working?
You'll get a much faster reply by posting back to the newsgroup.
There are lots of people who can help. Paul J. wrote: Thank you, Dave. I tried your code and it didn't work exactly as my SendKeys code, but I think it's close. I'll try tweaking a few references and see if that fixes it. If I can't get it to work the same, would you mind if I sent you the workbook to take a quick look? Thanks again, Paul ---------------------------------------------- "Dave Peterson" wrote: I've never used Sendkeys this much, but maybe it sure looks like showing those dialogs is flashing before my eyes. I got confused over what was happening, but your code might not be too difficult to change. And it may be easier to debug. I think I have the syntax correct--but I'm not sure I have the ranges correct. If you print this and try it manually, you may be able to match up the addresses that should change: Option Explicit Private Sub cmdApp_Click() Dim iRow As Long 'Turn off Screen Updating Application.ScreenUpdating = False 'Unprotect the worksheet With ActiveSheet .Unprotect Password:="excel" 'Goto Cell A22 'Insert a bunch of rows .Range("a22").Resize(7, 1).EntireRow.Insert 'Move cursor down 1 row For iRow = 23 To 30 Step 2 .Rows(iRow).RowHeight = 12.75 .Rows(iRow + 1).RowHeight = 3.75 Next iRow .Rows(31).RowHeight = 2.5 .Rows(32).Insert .Range("A34").Offset(0, 2).Resize(1, 6).Interior.ColorIndex = 1 .Range("a34").Offset(0, 2).Resize(4, 4).Copy _ Destination:=.Range("a25") Application.CutCopyMode = False .Range("a34").Offset(0, 1).Resize(1, 2).ClearContents .Range("a34").Offset(2, 1).ClearContents .Range("a34").Offset(2, 0).ClearContents .Range("a30").Select 'Re-protect the worksheet .Protect Password:="excel" End With 'Turn Screen Updating back on Application.ScreenUpdating = True End Sub The .offset(x,y) means go up/down x number of rows and go right/left y number of columns. so .offset(0,1) means stay in the same row and go to the right 1 column. The .resize(x,y) means to make the range x rows by y columns. ======== I'd say translating the things you did into Sendkeys was pretty impressive, though. Paul J. wrote: I'm using Office 2003 on Win XP Pro SP2, Dell PC. I have the following vba script in a spreadsheet, and I want to hide all the activity.. but the ScreenUpdating function doesn't seem to be hiding anything, tons of stuff still shows on screen. (Note: I just added in all the comments to this screen for readability, in my code they aren't there.) And please don't laugh that I'm using SendKeys statements, it was quick and I'm not a programmer. Thanks. ----------------------------------------- Private Sub cmdApp_Click() ' Turn off Screen Updating Application.ScreenUpdating = False ' Unprotect the worksheet Excel.SendKeys ("%(t)") Excel.SendKeys ("p") Excel.SendKeys ("p") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") ' Goto Cell A22 Excel.SendKeys ("{F5}") Excel.SendKeys ("{DELETE}") Excel.SendKeys ("A22") Excel.SendKeys ("{ENTER}") ' Select current row and next 7 rows Excel.SendKeys ("+({DOWN 7})") ' Insert a bunch of rows Excel.SendKeys ("%(i)") Excel.SendKeys ("r") ' Move cursor down 1 row Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re3.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re3.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re2.50{ENTER}") Excel.SendKeys ("{DOWN}") ' Insert another row Excel.SendKeys ("%(i)") Excel.SendKeys ("r") ' Move cursor two cells to the right Excel.SendKeys ("{RIGHT 2}") ' Select 6 columns wide (blank row that was just inserted) Excel.SendKeys ("+({RIGHT 6})") ' Format cell pattern to be solid black Excel.SendKeys ("%(o)ebp{TAB}{DOWN}{ENTER}{TAB 2}{ENTER}") ' Go down two rows Excel.SendKeys ("{DOWN 2}") ' Select 3 columns and 4 rows Excel.SendKeys ("+({RIGHT 3}{DOWN 4})") ' Copy selected rows Excel.SendKeys ("^(c)") ' Move curser up 9 rows Excel.SendKeys ("{UP 9}") ' Paste rows from previous copy then ESC out of copy sequence Excel.SendKeys ("^(v)") Excel.SendKeys ("{ESC}") ' Move right one cell, delete its contents Excel.SendKeys ("{RIGHT}") Excel.SendKeys ("{DELETE}") ' Move right two cells, delete its contents Excel.SendKeys ("{RIGHT 2}") Excel.SendKeys ("{DELETE}") ' Move down 2 and left 1 cell, delete its contents Excel.SendKeys ("{DOWN 2}{LEFT}{DELETE}") ' Move down 2 cells, delete its contents Excel.SendKeys ("{DOWN 2}{DELETE}") ' Move up 4 cells and left 1 cell Excel.SendKeys ("{UP 4}{LEFT}") ' Re-protect the worksheet Excel.SendKeys ("%(t)pp") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") ' Turn Screen Updating back on Application.ScreenUpdating = True End Sub ~ Paul -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ScreenUpdating function not working?
Dave, I got it figured out. There was no way for you to know about various
merged cells and what the end result was supposed to look like, so you did a great job estimating. After modifying a few references and removing the For/Next loop I got it to work perfectly. My new code is shown below. Thanks again, now I can build upon these techniques! ~ Paul J. ~ Option Explicit Private Sub cmdApp_Click() '[Removed Dim statement (for iRows) since For/Next loop was removed] 'Turn off Screen Updating Application.ScreenUpdating = False 'Unprotect the worksheet With ActiveSheet .Unprotect Password:="excel" 'Goto Cell A22 'Insert a bunch of rows '[I modified the Resize property] .Range("a22").Resize(9, 1).EntireRow.Insert '[Removed the For/Next loop and set specific row references] .Rows(23).RowHeight = 12.75 .Rows(24).RowHeight = 3.75 .Rows(25).RowHeight = 12.75 .Rows(26).RowHeight = 3.75 .Rows(27).RowHeight = 12.75 .Rows(28).RowHeight = 12.75 .Rows(29).RowHeight = 2.25 .Rows(30).RowHeight = 2.25 .Rows(31).RowHeight = 4.5 '[Modified the ranges and removed the 'offset' methods] .Range("C30").Resize(1, 7).Interior.ColorIndex = 1 .Range("C32").Resize(6, 7).Copy Destination:=.Range("C23") Application.CutCopyMode = False '[Because these were merged cells it kept generating an error, ' so I modified the ranges to accommodate the merges.] .Range("D23:E23").ClearContents .Range("G23:I23").ClearContents .Range("D25:F25").ClearContents .Range("D27:I28").ClearContents .Range("D23:E23").Select 'Re-protect the worksheet .Protect Password:="excel" End With 'Turn Screen Updating back on Application.ScreenUpdating = True End Sub Private Sub cmdSubmit_Click() Excel.SendKeys ("%(f)") Excel.SendKeys ("da") End Sub ---------------------------------------------------------- "Dave Peterson" wrote: You'll get a much faster reply by posting back to the newsgroup. There are lots of people who can help. Paul J. wrote: Thank you, Dave. I tried your code and it didn't work exactly as my SendKeys code, but I think it's close. I'll try tweaking a few references and see if that fixes it. If I can't get it to work the same, would you mind if I sent you the workbook to take a quick look? Thanks again, Paul ---------------------------------------------- "Dave Peterson" wrote: I've never used Sendkeys this much, but maybe it sure looks like showing those dialogs is flashing before my eyes. I got confused over what was happening, but your code might not be too difficult to change. And it may be easier to debug. I think I have the syntax correct--but I'm not sure I have the ranges correct. If you print this and try it manually, you may be able to match up the addresses that should change: Option Explicit Private Sub cmdApp_Click() Dim iRow As Long 'Turn off Screen Updating Application.ScreenUpdating = False 'Unprotect the worksheet With ActiveSheet .Unprotect Password:="excel" 'Goto Cell A22 'Insert a bunch of rows .Range("a22").Resize(7, 1).EntireRow.Insert 'Move cursor down 1 row For iRow = 23 To 30 Step 2 .Rows(iRow).RowHeight = 12.75 .Rows(iRow + 1).RowHeight = 3.75 Next iRow .Rows(31).RowHeight = 2.5 .Rows(32).Insert .Range("A34").Offset(0, 2).Resize(1, 6).Interior.ColorIndex = 1 .Range("a34").Offset(0, 2).Resize(4, 4).Copy _ Destination:=.Range("a25") Application.CutCopyMode = False .Range("a34").Offset(0, 1).Resize(1, 2).ClearContents .Range("a34").Offset(2, 1).ClearContents .Range("a34").Offset(2, 0).ClearContents .Range("a30").Select 'Re-protect the worksheet .Protect Password:="excel" End With 'Turn Screen Updating back on Application.ScreenUpdating = True End Sub The .offset(x,y) means go up/down x number of rows and go right/left y number of columns. so .offset(0,1) means stay in the same row and go to the right 1 column. The .resize(x,y) means to make the range x rows by y columns. ======== I'd say translating the things you did into Sendkeys was pretty impressive, though. Paul J. wrote: I'm using Office 2003 on Win XP Pro SP2, Dell PC. I have the following vba script in a spreadsheet, and I want to hide all the activity.. but the ScreenUpdating function doesn't seem to be hiding anything, tons of stuff still shows on screen. (Note: I just added in all the comments to this screen for readability, in my code they aren't there.) And please don't laugh that I'm using SendKeys statements, it was quick and I'm not a programmer. Thanks. ----------------------------------------- Private Sub cmdApp_Click() ' Turn off Screen Updating Application.ScreenUpdating = False ' Unprotect the worksheet Excel.SendKeys ("%(t)") Excel.SendKeys ("p") Excel.SendKeys ("p") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") ' Goto Cell A22 Excel.SendKeys ("{F5}") Excel.SendKeys ("{DELETE}") Excel.SendKeys ("A22") Excel.SendKeys ("{ENTER}") ' Select current row and next 7 rows Excel.SendKeys ("+({DOWN 7})") ' Insert a bunch of rows Excel.SendKeys ("%(i)") Excel.SendKeys ("r") ' Move cursor down 1 row Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re3.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re3.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re2.50{ENTER}") Excel.SendKeys ("{DOWN}") ' Insert another row Excel.SendKeys ("%(i)") Excel.SendKeys ("r") ' Move cursor two cells to the right Excel.SendKeys ("{RIGHT 2}") ' Select 6 columns wide (blank row that was just inserted) Excel.SendKeys ("+({RIGHT 6})") ' Format cell pattern to be solid black Excel.SendKeys ("%(o)ebp{TAB}{DOWN}{ENTER}{TAB 2}{ENTER}") ' Go down two rows Excel.SendKeys ("{DOWN 2}") ' Select 3 columns and 4 rows Excel.SendKeys ("+({RIGHT 3}{DOWN 4})") ' Copy selected rows Excel.SendKeys ("^(c)") ' Move curser up 9 rows Excel.SendKeys ("{UP 9}") ' Paste rows from previous copy then ESC out of copy sequence Excel.SendKeys ("^(v)") Excel.SendKeys ("{ESC}") ' Move right one cell, delete its contents Excel.SendKeys ("{RIGHT}") Excel.SendKeys ("{DELETE}") ' Move right two cells, delete its contents Excel.SendKeys ("{RIGHT 2}") Excel.SendKeys ("{DELETE}") ' Move down 2 and left 1 cell, delete its contents Excel.SendKeys ("{DOWN 2}{LEFT}{DELETE}") ' Move down 2 cells, delete its contents Excel.SendKeys ("{DOWN 2}{DELETE}") ' Move up 4 cells and left 1 cell Excel.SendKeys ("{UP 4}{LEFT}") ' Re-protect the worksheet Excel.SendKeys ("%(t)pp") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") ' Turn Screen Updating back on Application.ScreenUpdating = True End Sub ~ Paul -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ScreenUpdating function not working?
Glad you got it working. And with a little bit of experimentation, you'll find
it much easier than using the Sendkeys stuff. And if you want to email something, you may want to take a look at Ron de Bruin's page. http://www.rondebruin.nl/sendmail.htm He has lots of sample code for sending stuff out. Paul J. wrote: Dave, I got it figured out. There was no way for you to know about various merged cells and what the end result was supposed to look like, so you did a great job estimating. After modifying a few references and removing the For/Next loop I got it to work perfectly. My new code is shown below. Thanks again, now I can build upon these techniques! ~ Paul J. ~ Option Explicit Private Sub cmdApp_Click() '[Removed Dim statement (for iRows) since For/Next loop was removed] 'Turn off Screen Updating Application.ScreenUpdating = False 'Unprotect the worksheet With ActiveSheet .Unprotect Password:="excel" 'Goto Cell A22 'Insert a bunch of rows '[I modified the Resize property] .Range("a22").Resize(9, 1).EntireRow.Insert '[Removed the For/Next loop and set specific row references] .Rows(23).RowHeight = 12.75 .Rows(24).RowHeight = 3.75 .Rows(25).RowHeight = 12.75 .Rows(26).RowHeight = 3.75 .Rows(27).RowHeight = 12.75 .Rows(28).RowHeight = 12.75 .Rows(29).RowHeight = 2.25 .Rows(30).RowHeight = 2.25 .Rows(31).RowHeight = 4.5 '[Modified the ranges and removed the 'offset' methods] .Range("C30").Resize(1, 7).Interior.ColorIndex = 1 .Range("C32").Resize(6, 7).Copy Destination:=.Range("C23") Application.CutCopyMode = False '[Because these were merged cells it kept generating an error, ' so I modified the ranges to accommodate the merges.] .Range("D23:E23").ClearContents .Range("G23:I23").ClearContents .Range("D25:F25").ClearContents .Range("D27:I28").ClearContents .Range("D23:E23").Select 'Re-protect the worksheet .Protect Password:="excel" End With 'Turn Screen Updating back on Application.ScreenUpdating = True End Sub Private Sub cmdSubmit_Click() Excel.SendKeys ("%(f)") Excel.SendKeys ("da") End Sub ---------------------------------------------------------- "Dave Peterson" wrote: You'll get a much faster reply by posting back to the newsgroup. There are lots of people who can help. Paul J. wrote: Thank you, Dave. I tried your code and it didn't work exactly as my SendKeys code, but I think it's close. I'll try tweaking a few references and see if that fixes it. If I can't get it to work the same, would you mind if I sent you the workbook to take a quick look? Thanks again, Paul ---------------------------------------------- "Dave Peterson" wrote: I've never used Sendkeys this much, but maybe it sure looks like showing those dialogs is flashing before my eyes. I got confused over what was happening, but your code might not be too difficult to change. And it may be easier to debug. I think I have the syntax correct--but I'm not sure I have the ranges correct. If you print this and try it manually, you may be able to match up the addresses that should change: Option Explicit Private Sub cmdApp_Click() Dim iRow As Long 'Turn off Screen Updating Application.ScreenUpdating = False 'Unprotect the worksheet With ActiveSheet .Unprotect Password:="excel" 'Goto Cell A22 'Insert a bunch of rows .Range("a22").Resize(7, 1).EntireRow.Insert 'Move cursor down 1 row For iRow = 23 To 30 Step 2 .Rows(iRow).RowHeight = 12.75 .Rows(iRow + 1).RowHeight = 3.75 Next iRow .Rows(31).RowHeight = 2.5 .Rows(32).Insert .Range("A34").Offset(0, 2).Resize(1, 6).Interior.ColorIndex = 1 .Range("a34").Offset(0, 2).Resize(4, 4).Copy _ Destination:=.Range("a25") Application.CutCopyMode = False .Range("a34").Offset(0, 1).Resize(1, 2).ClearContents .Range("a34").Offset(2, 1).ClearContents .Range("a34").Offset(2, 0).ClearContents .Range("a30").Select 'Re-protect the worksheet .Protect Password:="excel" End With 'Turn Screen Updating back on Application.ScreenUpdating = True End Sub The .offset(x,y) means go up/down x number of rows and go right/left y number of columns. so .offset(0,1) means stay in the same row and go to the right 1 column. The .resize(x,y) means to make the range x rows by y columns. ======== I'd say translating the things you did into Sendkeys was pretty impressive, though. Paul J. wrote: I'm using Office 2003 on Win XP Pro SP2, Dell PC. I have the following vba script in a spreadsheet, and I want to hide all the activity.. but the ScreenUpdating function doesn't seem to be hiding anything, tons of stuff still shows on screen. (Note: I just added in all the comments to this screen for readability, in my code they aren't there.) And please don't laugh that I'm using SendKeys statements, it was quick and I'm not a programmer. Thanks. ----------------------------------------- Private Sub cmdApp_Click() ' Turn off Screen Updating Application.ScreenUpdating = False ' Unprotect the worksheet Excel.SendKeys ("%(t)") Excel.SendKeys ("p") Excel.SendKeys ("p") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") ' Goto Cell A22 Excel.SendKeys ("{F5}") Excel.SendKeys ("{DELETE}") Excel.SendKeys ("A22") Excel.SendKeys ("{ENTER}") ' Select current row and next 7 rows Excel.SendKeys ("+({DOWN 7})") ' Insert a bunch of rows Excel.SendKeys ("%(i)") Excel.SendKeys ("r") ' Move cursor down 1 row Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re3.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re3.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re12.75{ENTER}") Excel.SendKeys ("{DOWN}") ' Format row height then go to next row Excel.SendKeys ("%(o)re2.50{ENTER}") Excel.SendKeys ("{DOWN}") ' Insert another row Excel.SendKeys ("%(i)") Excel.SendKeys ("r") ' Move cursor two cells to the right Excel.SendKeys ("{RIGHT 2}") ' Select 6 columns wide (blank row that was just inserted) Excel.SendKeys ("+({RIGHT 6})") ' Format cell pattern to be solid black Excel.SendKeys ("%(o)ebp{TAB}{DOWN}{ENTER}{TAB 2}{ENTER}") ' Go down two rows Excel.SendKeys ("{DOWN 2}") ' Select 3 columns and 4 rows Excel.SendKeys ("+({RIGHT 3}{DOWN 4})") ' Copy selected rows Excel.SendKeys ("^(c)") ' Move curser up 9 rows Excel.SendKeys ("{UP 9}") ' Paste rows from previous copy then ESC out of copy sequence Excel.SendKeys ("^(v)") Excel.SendKeys ("{ESC}") ' Move right one cell, delete its contents Excel.SendKeys ("{RIGHT}") Excel.SendKeys ("{DELETE}") ' Move right two cells, delete its contents Excel.SendKeys ("{RIGHT 2}") Excel.SendKeys ("{DELETE}") ' Move down 2 and left 1 cell, delete its contents Excel.SendKeys ("{DOWN 2}{LEFT}{DELETE}") ' Move down 2 cells, delete its contents Excel.SendKeys ("{DOWN 2}{DELETE}") ' Move up 4 cells and left 1 cell Excel.SendKeys ("{UP 4}{LEFT}") ' Re-protect the worksheet Excel.SendKeys ("%(t)pp") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") Excel.SendKeys ("excel") Excel.SendKeys ("{ENTER}") ' Turn Screen Updating back on Application.ScreenUpdating = True End Sub ~ Paul -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
| screenupdating = false not working in Workbook_open sub | Excel Programming | |||
| Application.ScreenUpdating function question | Excel Worksheet Functions | |||
| Application.screenupdating is not working in Excel 2002 though it. | Excel Programming | |||
| Application.Screenupdating not "always" working? | Excel Programming | |||
![Paul J.[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
