CODEWITHSUNDEEP
vbaexcel
Gopala Krishnan Sankaran
Gopala Krishnan Sankaran

Reputation: 53

Excel VBA - Cannot Close New Workbook created using Workbook.Add

I have the following code that creates a new workbook and populates it with some data. 

Dim wb as Workbook
Set wb = Workbooks.Add
wb.Range("A1") = "Dummy Text" 'Some code that populates data
wb.Activate 'wb.Activate or ThisWorkbook.Activate has no effect

The new workbook opens fine. I am able to minimise it, use the menus, etc. but am unable to click the close button.

If I go back to the original workbook that has the macro and come back to the new workbook, I can close.

How do I overcome this problem? Out of ideas unfortunately.

My desidred use case

Step 1 : User clicks a button -> A new workbook opens up with data

Step 2 : User reads the information in the new workbook and decides to save / close

But the close button in the new workbook won't work until the user goes back to the original workbook and comes again to the new workbook.

Upvotes: 1

Views: 1361

Answers (4)

lord.cho
lord.cho

Reputation: 1

When displaying your userform, make sure you use ".Show 0".

Upvotes: 0

Amine Soumiaa
Amine Soumiaa

Reputation: 693

I think you need to add this line:

Workbooks("source").Close

if you want to close the current workbook , try this :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Value = "CLOSE" Then
        ThisWorkbook.Saved = True
        ThisWorkbook.Close
    End If
End Sub

Upvotes: 0

Kellsens
Kellsens

Reputation: 312

after your code

Dim wb as Workbook
Set wb = Workbooks.Add
wb.Range("A1") = "Dummy Text"

insert

set wb = nothing

this will release your new workbook and you can close using the "X" in the top right.

P.S.: Sorry my english

Upvotes: 1

ali srn
ali srn

Reputation: 573

Sub Button1_Click()
Dim wb As Workbook
Set wb = Workbooks.Add
wb.Sheets("Sheet1").Range("A1") = "Dummy Text" 'Some code that populates data
wb.Activate 'wb.Activate or ThisWorkbook.Activate has no effect
End Sub

That worked fine on my excel. I click the close button and it has asked me if i want to save or not.

Upvotes: 0

Related Questions