arnoldasz
06-24-2018, 10:44 AM
Hello,
I have made an function that creates new workbook (Workbooks.Add) and stores some info from the array to the first sheet of created workbook. Workbook is created with user form button click. I am going to explain my problem through steps:
1. I open any excel file, for example "book1.xlsx"
2. Run my userform and click button
3. Now "newbook.xlsx" is created and opened
But now, until I have not closed userform, I can't copy any info from 'newbook.xlsx' and if I try to close 'newbook.xlsx', I get a message 'Do you really want to close 'book1.xlsx' (wrong workbook name)
If I close userform, everything works fine. Maybe someone have any ideas why it's happening? Function:
Private Function showGlandsResult(arr() As String)
'On Error GoTo HandleErrors
Application.DisplayAlerts = False
Dim arrLength As Long
Dim newWBook As Workbook
Dim i As Long
Dim j As Long
Dim startRow As Long
Dim curRow As Long
arrLength = UBound(arr)
curRow = 2
Set newWBook = Workbooks.Add(xlWBATWorksheet)
'Surasom masyvo reiksmes y nauja knyga
With newWBook.Worksheets(1)
.Name = "Sandarikliai"
.Cells(1, RES_CELL_DESC) = "Kabelis"
.Cells(1, RES_CELL_GLND) = "Sandariklis"
.Cells(1, RES_CELL_MANU) = "Gamintojas"
.Cells(1, RES_CELL_CODE) = "Kodas"
.Cells(1, RES_CELL_QUAN) = "Kiekis"
'reikia ysitikinti, kad RES_CALL_DESC reiksme yra maziausia, o RES_CELL_QUAN - didziausia,
'antraip kodas normaliai neveiks.
With .Range(.Cells(1, RES_CELL_DESC), .Cells(1, RES_CELL_QUAN))
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
For i = 1 To UBound(arr)
'kad zinotume, kurias eilutes sujungti
startRow = curRow
For j = 1 To UBound(arr, 2)
If arr(i, j, RES_CELL_DESC) <> vbNullString Then
.Cells(curRow, RES_CELL_DESC) = arr(i, j, RES_CELL_DESC)
.Cells(curRow, RES_CELL_GLND) = arr(i, j, RES_CELL_GLND)
.Cells(curRow, RES_CELL_MANU) = arr(i, j, RES_CELL_MANU)
.Cells(curRow, RES_CELL_CODE) = arr(i, j, RES_CELL_CODE)
.Cells(curRow, RES_CELL_QUAN) = arr(i, j, RES_CELL_QUAN)
curRow = curRow + 1
End If
Next j
'sujungiam eilutes, kuriose pasikartoja kabelio pavadinimas
.Range(.Cells(startRow, RES_CELL_DESC), .Cells(curRow - 1, RES_CELL_DESC)).Merge
Next i
'sulygiuojam ir sutalpinam viska y stulpelius
With .Range(.Cells(2, RES_CELL_GLND), .Cells(curRow - 1, RES_CELL_QUAN))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
.Range(.Cells(1, RES_CELL_DESC), .Cells(curRow - 1, RES_CELL_QUAN)).Columns.EntireColumn.AutoFit
End With
newWBook.Activate
HandleErrors:
Application.DisplayAlerts = True
End Function
I have made an function that creates new workbook (Workbooks.Add) and stores some info from the array to the first sheet of created workbook. Workbook is created with user form button click. I am going to explain my problem through steps:
1. I open any excel file, for example "book1.xlsx"
2. Run my userform and click button
3. Now "newbook.xlsx" is created and opened
But now, until I have not closed userform, I can't copy any info from 'newbook.xlsx' and if I try to close 'newbook.xlsx', I get a message 'Do you really want to close 'book1.xlsx' (wrong workbook name)
If I close userform, everything works fine. Maybe someone have any ideas why it's happening? Function:
Private Function showGlandsResult(arr() As String)
'On Error GoTo HandleErrors
Application.DisplayAlerts = False
Dim arrLength As Long
Dim newWBook As Workbook
Dim i As Long
Dim j As Long
Dim startRow As Long
Dim curRow As Long
arrLength = UBound(arr)
curRow = 2
Set newWBook = Workbooks.Add(xlWBATWorksheet)
'Surasom masyvo reiksmes y nauja knyga
With newWBook.Worksheets(1)
.Name = "Sandarikliai"
.Cells(1, RES_CELL_DESC) = "Kabelis"
.Cells(1, RES_CELL_GLND) = "Sandariklis"
.Cells(1, RES_CELL_MANU) = "Gamintojas"
.Cells(1, RES_CELL_CODE) = "Kodas"
.Cells(1, RES_CELL_QUAN) = "Kiekis"
'reikia ysitikinti, kad RES_CALL_DESC reiksme yra maziausia, o RES_CELL_QUAN - didziausia,
'antraip kodas normaliai neveiks.
With .Range(.Cells(1, RES_CELL_DESC), .Cells(1, RES_CELL_QUAN))
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
For i = 1 To UBound(arr)
'kad zinotume, kurias eilutes sujungti
startRow = curRow
For j = 1 To UBound(arr, 2)
If arr(i, j, RES_CELL_DESC) <> vbNullString Then
.Cells(curRow, RES_CELL_DESC) = arr(i, j, RES_CELL_DESC)
.Cells(curRow, RES_CELL_GLND) = arr(i, j, RES_CELL_GLND)
.Cells(curRow, RES_CELL_MANU) = arr(i, j, RES_CELL_MANU)
.Cells(curRow, RES_CELL_CODE) = arr(i, j, RES_CELL_CODE)
.Cells(curRow, RES_CELL_QUAN) = arr(i, j, RES_CELL_QUAN)
curRow = curRow + 1
End If
Next j
'sujungiam eilutes, kuriose pasikartoja kabelio pavadinimas
.Range(.Cells(startRow, RES_CELL_DESC), .Cells(curRow - 1, RES_CELL_DESC)).Merge
Next i
'sulygiuojam ir sutalpinam viska y stulpelius
With .Range(.Cells(2, RES_CELL_GLND), .Cells(curRow - 1, RES_CELL_QUAN))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
.Range(.Cells(1, RES_CELL_DESC), .Cells(curRow - 1, RES_CELL_QUAN)).Columns.EntireColumn.AutoFit
End With
newWBook.Activate
HandleErrors:
Application.DisplayAlerts = True
End Function