Excel VBA Modeless Userform Disabling Clipboard

Question

I'm running a desktop macro that at various times runs excel macros. I use modeless Userforms that appear at certain points to prompt the Desktop macro to begin certain procedures.

The code in question comes in when data should be input. UserForm1 displays, which prompts the desktop macro to copy from notepad into excel using the text import wizard (the data is .csv in notepad when given to me).

If the data being input is too long for one sheet, a separate procedure creates other sheets. The input macro tests for those other sheets. If they exist, they will generate other Userforms to prompt data to be input into the other sheets.

It was functional for a time. I changed the code and declared some Private Constants for module-wide use, but now the clipboard remains greyed out when the UserForm shows. I'm stumped.

The data is dated, and the macro loops through inputted data (one day at a time) on the given sheets.

Private Constants:

Private Const DayNumber As Integer 'Day # being cycled through
Private Const MonthDayMaximum As Integer 'Maximum # of days in month

Code:

Private Sub InputData ()


Dim Sheet2Exists As Boolean
Dim i As Long

DayNumber = 1 'First day in month
MonthDayMaximum = InputBox("Input Days in Month","Input Prompt",0) + 1
Sheet2Exists = False

Application.ScreenUpdating = False


Do While DayNumber <> MonthDayMaximum

For i = 1 to Worksheets.Count
    If Worksheets(i).Name = "Sheet1 (2)" Then 'Testing for other sheet added previously
        Sheet2Exists = True
    End If
Next i


Application.ScreenUpdating = True    

Sheets("Sheet1").Activate
UserForm1.Show vbModeless
     Do While UserForm1.Visible 'Desktop macro inputs data, then closes Userform
         DoEvents
     Loop
MsgBox "Sheet1 Data Inputted", vbOKOnly, "Sheet1" 'Tells desktop macro to move on

If Sheet2Exists = True Then
    Sheets("Sheet1 (2)").Activate
    UserForm2.Show vbModeless 'Macro recognizes different userforms
    Do While UserForm2.Visible
        DoEvents
    Loop
MsgBox "Sheet1 (2) Data Inputted", vbOKOnly, "Sheet1 (2)"
End If


Application.ScreenUpdating = False

Call Classify 'Follows other procedures for inputted data

DayNumber = DayNumber + 1

Loop


End Sub

AJD · Accepted Answer · 2018-05-14 20:17:07Z

4

I would guess that the greying-out indicates an application error. And it is not hard to find.

Use Option Explicit always. This, in conjunction with Debug -> Compile will help expose coding flaws.

The first problem I saw was your use of the Const. They aren't.

Private Const DayNumber As Integer 'Day # being cycled through
Private Const MonthDayMaximum As Integer 'Maximum # of days in month

Should be declared like the following if you really wanted them to be constant:

Private Const DayNumber As Integer = <something> 'Day # being cycled through
Private Const MonthDayMaximum As Integer = <something> 'Maximum # of days in month

Which immediately tells you that the Const is the wrong element to use (after all Day and Days in Months are not constant).

And then, in your main code, you try to assign something to a Const which is a big no-no.

DayNumber = 1 ' Bad!
MonthDayMaximum = InputBox("Input Days in Month","Input Prompt",0) + 1 ` Bad!

If you wanted them to be module wide, then declare them at the top of the module like:

Private DayNumber As Integer 'Day # being cycled through
Private MonthDayMaximum As Integer 'Maximum # of days in month

Note that they are not Const. That should work, but that also screams "code stink" and breaks the basics of good and OOP-ish programming.

answered May 14, 2018 at 20:17

AJD

2,4682 gold badges15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pvxl1989 Over a year ago

Thanks for the answer. Yeah, I did some re-writing since I posted and that was one of my changes. I'm still on the hunt, and I'll update after a bit more work.

Collectives™ on Stack Overflow

Excel VBA Modeless Userform Disabling Clipboard

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related