Nested With statements

G

gz

Hi, I have a form on which there is a button. click the button first time,
it works fine and the excel file is opened, however, if I click the button
second time, it stopped at the first line of a nested WITH statement. Why?
How to solve this problem? Thanks!

Private Sub cmdPrint_Click()
Dim i As Integer, j As Integer, k As Integer
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.Workbooks.Add
With xlApp
.StandardFont = "Times New Roman"
.Visible = True
.Rows("1:1").RowHeight = 25
.Range("A1:H1").Select
With Selection
'Stop at this line when excute this sub second time:
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
.Font.Size = 24
.Font.Bold = True
.Value = "Statement"
End With

End With
Set xlApp = Nothing
End Sub
 
G

gz

This error may not occur if the first instant excel file is not closed.
However, if I do not close the first instant of excel file, The .Value =
"Statement" will be show on the first instant not the second instant od
excel file.
 
D

Dirk Goldgar

gz said:
Hi, I have a form on which there is a button. click the button first
time, it works fine and the excel file is opened, however, if I click
the button second time, it stopped at the first line of a nested WITH
statement. Why? How to solve this problem? Thanks!

Private Sub cmdPrint_Click()
Dim i As Integer, j As Integer, k As Integer
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.Workbooks.Add
With xlApp
.StandardFont = "Times New Roman"
.Visible = True
.Rows("1:1").RowHeight = 25
.Range("A1:H1").Select
With Selection
'Stop at this line when excute this sub second time:
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
.Font.Size = 24
.Font.Bold = True
.Value = "Statement"
End With

End With
Set xlApp = Nothing
End Sub
Click to expand...

Does the problem go away if you add the "dot" (.) before "Selection":

With .Selection

?
 
Top