ScreenUpdating function not working?

G

Guest

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
 
D

Dave Peterson

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.
 
G

Guest

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

----------------------------------------------
 
D

Dave Peterson

You'll get a much faster reply by posting back to the newsgroup.

There are lots of people who can help.
 
G

Guest

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

----------------------------------------------------------
 
D

Dave Peterson

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.

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

----------------------------------------------------------
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Running an Excel AddIn from Access in Current WorkBook....Help!!!! 5
For-Next by a row Counting consecutive Zero in the column 4
If specific cell is not empty, copy (or enter) formulas and format 2
Array with many cells from a row but not in order or together 11
Find Text and Insert Row Above 9
Help with combining cells 3
macro to show / hide grouping, linked to label button 3
combining multiple rows into one row 2

Top