why do I have to reopen my project to use excel?

billy7978 · Sep 6, 2006

hi friends. I really have a serious problem. maybe it is easy for you but it is not for me.

I am leading excell with vb.
I have some merged cells. when I run the project it works. but if
I click button the second time it does not work. I see that the merged cells are unmerged. so I have to close my project and run again.

Scott Huish · Sep 6, 2006

That would explain the MDIform then, but sorry I don't have VB and it's been quite a while since I've done straight VB. I wish I could help, maybe someone else will be able to.

billy7978 · Sep 6, 2006

That would explain the MDIform then, but sorry I don't have VB and it's been quite a while since I've done straight VB. I wish I could help, maybe someone else will be able to.

thanks alot

Legacy 98055 · Sep 6, 2006

Billy. Send me your VB project files and I will at least see if I have the same problem here. What version of Excel? See email link below...

Billy. Send me your VB project files and I will at least see if I have the same problem here. What version of Excel? See email link below...
Also, when you merge cells, it is more safe to refer to the the entire range.

Instead of:
ws.Range("A" & 1).Value = "a long adress there is no problem here"
you might try:
ws.Range("A1:D1").Value = "a long adress there is no problem here"

I would retain a reference at the module level and then refer to the reference from that point on instead of working with addresses.

Edit. After trying your code, it bugged out.

Dim app As Object
Dim wb As Object
Dim ws As Object
Try:

Code:

Private Sub Command1_Click()
    Set app = CreateObject("Excel.Application")
    app.Visible = True
    
    
    Set wb = app.Workbooks.Add
    Set ws = wb.Worksheets(1)
    
    ws.Range(ws.Cells(1, 1), ws.Cells(1, 4)).Merge '....( a1 to a4)
    
    ws.Range("A" & 1).Value = "a long adress there is no problem here"
End Sub

billy7978 · Sep 7, 2006

i will be pleased if you can help me

hi right _click

here is the link for my project you can download and see what the problem is

http://www.hemenpaylas.com/download/1515841/excel_problem.zip.html

or you can see my codes from here

Private Sub Command1_Click()

Dim xlApp As Object
Dim xlWb As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Set xlWb = xlApp.Workbooks.Add
Set xlApp = xlWb.Worksheets(1)

xlApp.Range(Cells(1, 1), Cells(1, 5)).Merge
xlApp.Range("A" & 1).Value = " Billy7978 Educational Student Project but have problem with merged cells"
xlApp.Columns.WrapText = True

xlApp.Range(Cells(1, 1), Cells(1, 5)).HorizontalAlignment = xlCenter

xlApp.Columns.WrapText = True

xlApp.Rows("1:1").RowHeight = 30.57 '

End Sub

all the code is this. it is office (excel)XP

when i click second time
I always recive this error message : method 'cells' of object_Global failed

Legacy 98055 · Sep 7, 2006

Hi Billy.

The only reason your are getting away with compiling your example above is because you are late binding. I would assume that is why the compiler is not raising an error. Why the code is working to begin with is beyond me. It should not work the first time.

xlApp.Range(Cells(1, 1), Cells(1, 5)).Merge

Who does "Cells" belong to? It is an unqualified reference. VB is assuming that this must be a Global method but since it does not exist, you are getting the error. A more correct way to code this would be as such:

<table width="100%" border="1" bgcolor="White" style="filter

rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD> Private Sub Command1_Click()
 Dim xlApp As Object
 Dim xlWb As Object
 Dim xlWs As Object
 Dim xlRange As Object

 'create an application object and retain a reference
 Set xlApp = CreateObject("Excel.Application")
 xlApp.Visible = True

 'add a workbook and
 Set xlWb = xlApp.Workbooks.Add
 'grab a reference to the first worksheet
 Set xlWs = xlWb.Worksheets(1)

 'reference the range A1 to D1
 Set xlRange = xlWs.Range(xlWs.Cells(1, 1), xlWs.Cells(1, 5))

 'use the reference
 With xlRange
 .Merge
 .Value = " Billy7978 Educational Student Project but have problem with merged cells"
 .Columns.WrapText = True
 .HorizontalAlignment = xlCenter
 .Rows(1).RowHeight = 30.57 '
 End With
 End Sub
</td></tr></table><button onclick='document.all("972006215823734").value=document.all("972006215823734").value.replace(/ \s\s/g,"");document.all("972006215823734").value=document.all("972006215823734").value.replace(/ /g,"");window.clipboardData.setData("Text",document.all("972006215823734").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="972006215823734" wrap="virtual">
Private Sub Command1_Click()
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim xlRange As Object

'create an application object and retain a reference
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

'add a workbook and
Set xlWb = xlApp.Workbooks.Add
'grab a reference to the first worksheet
Set xlWs = xlWb.Worksheets(1)

'reference the range A1 to D1
Set xlRange = xlWs.Range(xlWs.Cells(1, 1), xlWs.Cells(1, 5))

'use the reference
With xlRange
.Merge
.Value = " Billy7978 Educational Student Project but have problem with merged cells"
.Columns.WrapText = True
.HorizontalAlignment = xlCenter
.Rows(1).RowHeight = 30.57 '
End With
End Sub</textarea>

billy7978 · Sep 8, 2006

thanks

Hi Billy.

The only reason your are getting away with compiling your example above is because you are late binding. I would assume that is why the compiler is not raising an error. Why the code is working to begin with is beyond me. It should not work the first time.

xlApp.Range(Cells(1, 1), Cells(1, 5)).Merge

Who does "Cells" belong to? It is an unqualified reference. VB is assuming that this must be a Global method but since it does not exist, you are getting the error. A more correct way to code this would be as such:

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD> Private Sub Command1_Click()
 Dim xlApp As Object
 Dim xlWb As Object
 Dim xlWs As Object
 Dim xlRange As Object

 'create an application object and retain a reference
 Set xlApp = CreateObject("Excel.Application")
 xlApp.Visible = True

 'add a workbook and
 Set xlWb = xlApp.Workbooks.Add
 'grab a reference to the first worksheet
 Set xlWs = xlWb.Worksheets(1)

 'reference the range A1 to D1
 Set xlRange = xlWs.Range(xlWs.Cells(1, 1), xlWs.Cells(1, 5))

 'use the reference
 With xlRange
 .Merge
 .Value = " Billy7978 Educational Student Project but have problem with merged cells"
 .Columns.WrapText = True
 .HorizontalAlignment = xlCenter
 .Rows(1).RowHeight = 30.57 '
 End With
 End Sub
</td></tr></table><button onclick='document.all("972006215823734").value=document.all("972006215823734").value.replace(/ \s\s/g,"");document.all("972006215823734").value=document.all("972006215823734").value.replace(/ /g,"");window.clipboardData.setData("Text",document.all("972006215823734").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="972006215823734" wrap="virtual">
Private Sub Command1_Click()
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim xlRange As Object

'create an application object and retain a reference
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

'add a workbook and
Set xlWb = xlApp.Workbooks.Add
'grab a reference to the first worksheet
Set xlWs = xlWb.Worksheets(1)

'reference the range A1 to D1
Set xlRange = xlWs.Range(xlWs.Cells(1, 1), xlWs.Cells(1, 5))

'use the reference
With xlRange
.Merge
.Value = " Billy7978 Educational Student Project but have problem with merged cells"
.Columns.WrapText = True
.HorizontalAlignment = xlCenter
.Rows(1).RowHeight = 30.57 '
End With
End Sub</textarea>

thanks right_click thanks very much problem is solved

why do I have to reopen my project to use excel?

billy7978

New Member

Scott Huish

MrExcel MVP

Excel Facts

billy7978

New Member

Legacy 98055

Guest

billy7978

New Member

Legacy 98055

Guest

billy7978

New Member

Similar threads

Forum statistics

Share this page

why do I have to reopen my project to use excel?

billy7978

New Member

Scott Huish

MrExcel MVP

Excel Facts

billy7978

New Member

Legacy 98055

Guest

billy7978

New Member

Legacy 98055

Guest

billy7978

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock