Hello,
I have VB code that allows the user to select a workbook and copy data automatically from the previous workbook to the new workbook. After this is performed, I want to call a Macro that will address a micro font bug with multiline. After the import is completed, I use the macro by using the single line at the end of the import
Call FixMultiline
I know it is running after the import because I purposely created an error and had it error out after the copy process.
If I manually run the macro, it works fine. I need to have the macro run after the import.
Here is the code I am using..
My Macro code is:
I have VB code that allows the user to select a workbook and copy data automatically from the previous workbook to the new workbook. After this is performed, I want to call a Macro that will address a micro font bug with multiline. After the import is completed, I use the macro by using the single line at the end of the import
Call FixMultiline
I know it is running after the import because I purposely created an error and had it error out after the copy process.
If I manually run the macro, it works fine. I need to have the macro run after the import.
Here is the code I am using..
Code:
Public Sub CopySMARTData(dmy As Integer)
CurWkbk.Activate
If preV13 Or PreV2 Then
'copy SMARTPlan data from pre version 2 files
CurWkbk.Worksheets("SMARTPlanReview").Range("A6:A6") = _
BudWkbk.Worksheets("SMARTPlan").Range("A16:A16").Value
CurWkbk.Worksheets("SMARTPlanReview").Range("A8:A8") = _
BudWkbk.Worksheets("SMARTPlan").Range("A18:A18").Value
CurWkbk.Worksheets("SMARTPlanReview").Range("A10:A10") = _
BudWkbk.Worksheets("SMARTPlan").Range("A20:A20").Value
CurWkbk.Worksheets("SMARTPlanReview").Range("A12:A12") = _
BudWkbk.Worksheets("SMARTPlan").Range("A22:A22").Value
CurWkbk.Worksheets("SMARTPlanReview").Range("A14:A14") = _
BudWkbk.Worksheets("SMARTPlan").Range("A26:A26").Value
Else
'copy SMARTPlan date from version 2
For Each strg In Array("A6:A6", "A8:A8", "A10:A10", _
"A12:A12", "A14:A14", "D17:D23", "G17:G23", "H17:H23")
BudWkbk.Worksheets("NewSMARTPlan").Range(strg).Copy
CurWkbk.Worksheets("SMARTPlanReview").Range(strg).PasteSpecial xlPasteValues
Next strg
For Each strg In Array("A17:A17", "A18:A18", "A19:A19", _
"A20:A20", "A21:A21", "A22:A22", "A23:A23")
CurWkbk.Worksheets("SMARTPlanReview").Range(strg) = _
BudWkbk.Worksheets("NewSMARTPlan").Range(strg).Value
Next
End If
CurWkbk.Worksheets("SMARTPlanReview").Activate
CurWkbk.Worksheets("SMARTPlanReview").Range("A5:A5").Select
Application.CutCopyMode = False
Call FixMultiline
End Sub
My Macro code is:
Code:
<code>Sub FixMultiline() ' Platform: Windows 8.0 ' Excel Version: 2013 ' Macro name: FixMultiline Macro ' Issue: ActiveX Text boxes have tiny text on focus (normal text after they lose focus) 'This will apply the fix to every TextBox on the specified sheets Dim myArray As Variant Dim x As Integer Dim mySheets As Variant mySheets = Array("Sheet1", "Sheet3") 'Define your array of sheet names For x = LBound(mySheets) To UBound(mySheets) 'Define start and end of array With Sheets(mySheets(x)) For i = 1 To .OLEObjects.Count If TypeOf .OLEObjects(i).Object Is MSForms.TextBox Then '.OLEObjects(i).Verb xlVerbPrimary 'Activate the current ActiveX Control (found this on one of your previous posts) .OLEObjects(i).Object.MultiLine = False 'Set MultiLine to False .OLEObjects(i).Object.WordWrap = False 'Set WordWrap to False .OLEObjects(i).Object.MultiLine = True 'Set Multiline to True .OLEObjects(i).Object.WordWrap = True 'Set WordWrap to True End If Next i End With Next x ' Loop! End Sub</code></pre>
Last edited: