Ribbon locks up/non responsive after running user form macro

sleuth

New Member
Joined
Jan 12, 2018
Messages
27
I have a userform which creates a new file and then pastes data into that file from the current file.
I'm having an issue though, in that the ribbon doesn't respond in the new file until I go into the current file and click around. I tried using CutCopyMode = False at the end of the userform routine, but it didn't work. I'm new to VBA and not sure what else to try.

Here's the code for the userform

<code>
Private Sub ExportSubmit_Click()


' Set sheets to copy from
Dim wsSupp As Worksheet
Dim wsPQE As Worksheet
Dim wsControls As Worksheet
Set wsSupp = Worksheets("Supplier_DL")
Set wsPQE = Worksheets("PQE_DL")
Set wsControls = Worksheets("Help & Controls")

' Open new workbook from template
Sheets("Help & Controls").Activate
Sheets("Help & Controls").Range("F3").Select
Dim templatePath As String: templatePath = ActiveCell.Value
Sheets("Help & Controls").Range("F4").Select
Dim trackerPath As String: trackerPath = ActiveCell.Value
Dim strTemplate As String: strTemplate = "QA_Tracker_Template.xltm"
Dim templateFile As String: templateFile = templatePath & strTemplate
Dim wb As Workbook
Set wb = Workbooks.Add(templateFile)

' Name and Save new workbook
Dim wbname As String: wbname = "QAL-" & Me.QALNumber.Value & " Distribution & Tracker.xlsm"
wb.SaveAs Filename:=trackerPath & wbname, FileFormat:=52

ThisWorkbook.Activate

Dim NumRows As Integer, NumCols As Integer


With wsSupp
NumRows = Sheets("Supplier_DL").UsedRange.Rows.Count
NumCols = Sheets("Supplier_DL").UsedRange.Columns.Count
End With


'Copy data to new file
ThisWorkbook.Worksheets("Supplier_DL").Range("A2:F" & NumRows).Copy Destination:= _
Workbooks(wbname).Worksheets("Distribution List Check").Range("B2:G" & NumRows)


Workbooks(wbname).Activate
Sheets("Distribution List Check").Rows("1:2").EntireRow.Insert
Sheets("Distribution List Check").Range("A1").Value = "QAL-" & Me.QALNumber.Value & ":"
Sheets("Distribution List Check").Range("B1").Value = Me.QALTitle.Value
Sheets("Distribution List Check").Range("A2").Value = "Due Date:"
Sheets("Distribution List Check").Range("B2").Value = Me.monthDue.Value & " " & Me.dayDue.Value & ", " & Me.yearDue.Value
Range("A1:B2").Font.ColorIndex = 0
Range("A1:B2").Font.Bold = True
Range("A1:A2").HorizontalAlignment = xlRight
Range("B1:B2").HorizontalAlignment = xlLeft
Range("1:2").EntireRow.Interior.Color = RGB(205, 205, 205)

Dim cell As Range

For Each cell In Range("A1:H2")
With cell.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With cell.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With cell.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With cell.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Next cell

'Clear input controls.
Me.QALNumber.Value = ""
Me.QALTitle.Value = ""
Me.monthDue.Value = ""
Me.dayDue.Value = ""
Me.yearDue.Value = ""
Unload Me



End Sub
</code>
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,214,944
Messages
6,122,387
Members
449,080
Latest member
Armadillos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top