Compile Error in Hidden Module - XL 97 - Built in XL 2000 (HELP)

[AustinMan]

I have a protected VBA project which I wrote code in XL 2000. Please HELP!!

I can not determine in the Module1 the compile error, all the code below is in Module1 and the Sub Showdialog is called on the Worksheet Selection change event in the workbook.

I have this statement and call ShowDialog
(Application.Run) on Worksheet Selection Change Event.

Why is this not working? I am at a loss. Thanks for your
help.

Thanks! George C.

Public Sub Decide2()
#If VBA6 Then
Call ShowModeless2
#Else
Job_Function.Show
#End If
End Sub

Public Sub ShowDialog()
Dim Msg As String
Dim Response As VbMsgBoxResult
Dim Style As MsoButtonStyle
Dim Title As String

Style = vbYesNo + vbCritical + vbDefaultButton2

Msg = "Do you want to add/change/delete job functions in
this cell?" & vbCrLf & vbCrLf & "If you do, you will need
to re-enter all the job functions again."
Title = "Job Title Selection Change"


If (((ActiveCell.Column = 4) Or (ActiveCell.Column = 5))
And ((ActiveCell.Address = "$D$11&quot Or (ActiveCell.Address
= &quot;$E$11&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5))
And ((ActiveCell.Address = &quot;$D$13&quot Or (ActiveCell.Address
= &quot;$E$13&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5))
And ((ActiveCell.Address = &quot;$D$15&quot Or (ActiveCell.Address
= &quot;$E$15&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5))
And ((ActiveCell.Address = &quot;$D$17&quot Or (ActiveCell.Address
= &quot;$E$17&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5))
And ((ActiveCell.Address = &quot;$D$19&quot Or (ActiveCell.Address
= &quot;$E$19&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5))
And ((ActiveCell.Address = &quot;$D$21&quot Or (ActiveCell.Address
= &quot;$E$21&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5))
And ((ActiveCell.Address = &quot;$D$23&quot Or (ActiveCell.Address
= &quot;$E$23&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5))
And ((ActiveCell.Address = &quot;$D$25&quot Or (ActiveCell.Address
= &quot;$E$25&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5))
And ((ActiveCell.Address = &quot;$D$27&quot Or (ActiveCell.Address
= &quot;$E$27&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5))
And ((ActiveCell.Address = &quot;$D$29&quot Or (ActiveCell.Address
= &quot;$E$29&quot) And (ActiveCell.Value <> &quot;&quot) Then

Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
ActiveSheet.Unprotect Password:=&quot;2Tough&quot;
Selection.Locked = True
ActiveSheet.Protect Password:=&quot;2Tough&quot;
ActiveSheet.Unprotect Password:=&quot;2Tough&quot;
Selection.Locked = False
ActiveSheet.Protect Password:=&quot;2Tough&quot;
ActiveCell.Offset(, 1).Activate
Exit Sub
Else
Job_Function.ListBox1.RowSource = &quot;&quot;
' Add some items to the ListBox
With Job_Function.ListBox1
.RowSource = &quot;&quot;
.AddItem &quot;1&quot;
.AddItem &quot;2&quot;
.AddItem &quot;3&quot;
.AddItem &quot;4&quot;
.AddItem &quot;5&quot;
.AddItem &quot;6&quot;
.AddItem &quot;7&quot;
.AddItem &quot;8&quot;
.AddItem &quot;9&quot;
.AddItem &quot;10&quot;
.AddItem &quot;11&quot;
.AddItem &quot;12&quot;
.AddItem &quot;13&quot;
.AddItem &quot;14&quot;
.AddItem &quot;15&quot;
.AddItem &quot;16&quot;
.AddItem &quot;17&quot;
.AddItem &quot;18&quot;
.AddItem &quot;19&quot;
.AddItem &quot;20&quot;
.AddItem &quot;21&quot;
.AddItem &quot;22&quot;
.AddItem &quot;23&quot;
.AddItem &quot;24&quot;
.AddItem &quot;25&quot;
.AddItem &quot;26&quot;
.AddItem &quot;27&quot;
.AddItem &quot;28&quot;
.AddItem &quot;29&quot;
.AddItem &quot;30&quot;
.AddItem &quot;31&quot;
End With

Call Decide2

End If
Else
Job_Function.ListBox1.RowSource = &quot;&quot;
' Add some items to the ListBox
With Job_Function.ListBox1
.RowSource = &quot;&quot;
.AddItem &quot;1&quot;
.AddItem &quot;2&quot;
.AddItem &quot;3&quot;
.AddItem &quot;4&quot;
.AddItem &quot;5&quot;
.AddItem &quot;6&quot;
.AddItem &quot;7&quot;
.AddItem &quot;8&quot;
.AddItem &quot;9&quot;
.AddItem &quot;10&quot;
.AddItem &quot;11&quot;
.AddItem &quot;12&quot;
.AddItem &quot;13&quot;
.AddItem &quot;14&quot;
.AddItem &quot;15&quot;
.AddItem &quot;16&quot;
.AddItem &quot;17&quot;
.AddItem &quot;18&quot;
.AddItem &quot;19&quot;
.AddItem &quot;20&quot;
.AddItem &quot;21&quot;
.AddItem &quot;22&quot;
.AddItem &quot;23&quot;
.AddItem &quot;24&quot;
.AddItem &quot;25&quot;
.AddItem &quot;26&quot;
.AddItem &quot;27&quot;
.AddItem &quot;28&quot;
.AddItem &quot;29&quot;
.AddItem &quot;30&quot;
.AddItem &quot;31&quot;
End With

Call Decide2

Exit Sub
End If
End Sub

 

[xlbo]

This is usually because you have made use of functionality that does not exist in xl97
In this case, what is &quot;Call ShowModeless2&quot; ??? I ask because xl97 does not support modeless userforms in the same way that 2000 does.....

Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[AustinMan]

Yes, the Call ShowModeless2 is the function the provides the option for XL 2000 for the optional argument of .show
Modal vbModaless.


I made another userform that insert rows and using that same syntax (IF THEN ELSE), it works for that userform.

I do not know if the Application.run function is the problem, I changed to Call ShowDialog. (no difference)


Is there something strange in ShowDialog?

I will really give you all I have in Module1, this time.


Public Sub CopyRange()
'Dim i As Single
'Dim Y As Single
'Dim Rng1 As Range
'Dim SheetName As Worksheet
'
'Y = 5
'
'Set SheetName = ActiveSheet
'
'For i = 1 To 15
' Application.EnableEvents = False
' Application.ScreenUpdating = False
' Application.Cursor = xlIBeam
' Worksheets(&quot;Sheet20&quot.Unprotect Password:=&quot;2Tough&quot;
' Worksheets(i).Unprotect Password:=&quot;2Tough&quot;
' Worksheets(&quot;Sheet&quot; & i & &quot;&quot.Activate
' Range(&quot;A11:L29&quot.Select
' Selection.Copy
' Range(&quot;A11&quot.Select
' Worksheets(&quot;Sheet20&quot.Activate
' Range(&quot;B&quot; & Y & &quot;&quot.Select
' Selection.PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True
' Y = Y + 20
' Worksheets(i).Protect Password:=&quot;2Tough&quot;, DrawingObjects:=True, Contents:=True, Scenarios:=True, userinterfaceonly:=True
'Next i
' Worksheets(&quot;Sheet20&quot.Protect Password:=&quot;2Tough&quot;
' Worksheets(SheetName.Name).Activate
' Range(&quot;A11&quot.Select
' Application.Cursor = xlDefault
' Application.EnableEvents = True
' Application.ScreenUpdating = True
End Sub

Public Sub CopyRange2()
'Dim i As Single
'Dim Y As Single
'Dim Rng1 As Range
'Dim SheetName As Worksheet
'
'Y = 5
'
'Set SheetName = ActiveSheet
'
'For i = 16 To 19
' Application.EnableEvents = False
' Application.ScreenUpdating = False
' Application.Cursor = xlIBeam
' Worksheets(&quot;Sheet21&quot.Unprotect Password:=&quot;2Tough&quot;
' Worksheets(i).Unprotect Password:=&quot;2Tough&quot;
' Worksheets(&quot;Sheet&quot; & i & &quot;&quot.Activate
' Range(&quot;A11:L29&quot.Select
' Selection.Copy
' Range(&quot;A11&quot.Select
' Worksheets(&quot;Sheet21&quot.Activate
' Range(&quot;B&quot; & Y & &quot;&quot.Select
' Selection.PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True
' Y = Y + 20
' Worksheets(i).Protect Password:=&quot;2Tough&quot;, DrawingObjects:=True, Contents:=True, Scenarios:=True, userinterfaceonly:=True
'Next i
' Worksheets(&quot;Sheet21&quot.Protect Password:=&quot;2Tough&quot;
' Worksheets(SheetName.Name).Activate
' Range(&quot;A11&quot.Select
' Application.Cursor = xlDefault
' Application.EnableEvents = True
' Application.ScreenUpdating = True
End Sub

Public Sub OpenVisual()
Application.VBE.MainWindow.Visible = True
End Sub

Public Sub Decide()
#If VBA6 Then
Call ShowModeless
#Else
Insert_Row_Form.Show
#End If
End Sub

Public Sub Decide2()
#If VBA6 Then
Call ShowModeless2
#Else
Job_Function.Show
#End If
End Sub

Public Sub ShowModeless2()
Job_Function.Show vbModeless
End Sub

Public Sub ShowModeless()
Insert_Row_Form.Show vbModeless
Insert_Row_Form.Enabled = True
End Sub

Public Sub TestPreview()
'Application.Dialogs(xlDialogPrintPreview).Show False
End Sub

Public Function SheetName(ref) As String
SheetName = ref.Parent.Name
End Function

Public Function ConcatRange(Cellblock As Range) As String

Dim Cell As Range

Application.Volatile True
For Each Cell In Cellblock
If Cell.Value <> &quot;&quot; Then
ConcatRange = ConcatRange & Cell.Value & &quot;,&quot;
End If
Next

If ConcatRange = &quot;&quot; Then
Exit Function
Else
ConcatRange = Left(ConcatRange, Len(ConcatRange) - 1)
End If

End Function

Public Sub ShowDialog()
Dim Msg As String
Dim Response As VbMsgBoxResult
Dim Style As MsoButtonStyle
Dim Title As String

Style = vbYesNo + vbCritical + vbDefaultButton2

Msg = &quot;Do you want to add/change/delete job functions in this cell?&quot; & vbCrLf & vbCrLf & &quot;If you do, you will need to re-enter all the job functions again.&quot;
Title = &quot;Job Title Selection Change&quot;


If (((ActiveCell.Column = 4) Or (ActiveCell.Column = 5)) And ((ActiveCell.Address = &quot;$D$11&quot Or (ActiveCell.Address = &quot;$E$11&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5)) And ((ActiveCell.Address = &quot;$D$13&quot Or (ActiveCell.Address = &quot;$E$13&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5)) And ((ActiveCell.Address = &quot;$D$15&quot Or (ActiveCell.Address = &quot;$E$15&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5)) And ((ActiveCell.Address = &quot;$D$17&quot Or (ActiveCell.Address = &quot;$E$17&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5)) And ((ActiveCell.Address = &quot;$D$19&quot Or (ActiveCell.Address = &quot;$E$19&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5)) And ((ActiveCell.Address = &quot;$D$21&quot Or (ActiveCell.Address = &quot;$E$21&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5)) And ((ActiveCell.Address = &quot;$D$23&quot Or (ActiveCell.Address = &quot;$E$23&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5)) And ((ActiveCell.Address = &quot;$D$25&quot Or (ActiveCell.Address = &quot;$E$25&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5)) And ((ActiveCell.Address = &quot;$D$27&quot Or (ActiveCell.Address = &quot;$E$27&quot) And (ActiveCell.Value <> &quot;&quot) Or _
(((ActiveCell.Column = 4) Or (ActiveCell.Column = 5)) And ((ActiveCell.Address = &quot;$D$29&quot Or (ActiveCell.Address = &quot;$E$29&quot) And (ActiveCell.Value <> &quot;&quot) Then

Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
ActiveSheet.Unprotect Password:=&quot;2Tough&quot;
Selection.Locked = True
ActiveSheet.Protect Password:=&quot;2Tough&quot;
ActiveSheet.Unprotect Password:=&quot;2Tough&quot;
Selection.Locked = False
ActiveSheet.Protect Password:=&quot;2Tough&quot;
ActiveCell.Offset(, 1).Activate
Exit Sub
Else
Job_Function.ListBox1.RowSource = &quot;&quot;
' Add some items to the ListBox
With Job_Function.ListBox1
.RowSource = &quot;&quot;
.AddItem &quot;1&quot;
.AddItem &quot;2&quot;
.AddItem &quot;3&quot;
.AddItem &quot;4&quot;
.AddItem &quot;5&quot;
.AddItem &quot;6&quot;
.AddItem &quot;7&quot;
.AddItem &quot;8&quot;
.AddItem &quot;9&quot;
.AddItem &quot;10&quot;
.AddItem &quot;11&quot;
.AddItem &quot;12&quot;
.AddItem &quot;13&quot;
.AddItem &quot;14&quot;
.AddItem &quot;15&quot;
.AddItem &quot;16&quot;
.AddItem &quot;17&quot;
.AddItem &quot;18&quot;
.AddItem &quot;19&quot;
.AddItem &quot;20&quot;
.AddItem &quot;21&quot;
.AddItem &quot;22&quot;
.AddItem &quot;23&quot;
.AddItem &quot;24&quot;
.AddItem &quot;25&quot;
.AddItem &quot;26&quot;
.AddItem &quot;27&quot;
.AddItem &quot;28&quot;
.AddItem &quot;29&quot;
.AddItem &quot;30&quot;
.AddItem &quot;31&quot;
End With

Call Decide2

End If
Else
Job_Function.ListBox1.RowSource = &quot;&quot;
' Add some items to the ListBox
With Job_Function.ListBox1
.RowSource = &quot;&quot;
.AddItem &quot;1&quot;
.AddItem &quot;2&quot;
.AddItem &quot;3&quot;
.AddItem &quot;4&quot;
.AddItem &quot;5&quot;
.AddItem &quot;6&quot;
.AddItem &quot;7&quot;
.AddItem &quot;8&quot;
.AddItem &quot;9&quot;
.AddItem &quot;10&quot;
.AddItem &quot;11&quot;
.AddItem &quot;12&quot;
.AddItem &quot;13&quot;
.AddItem &quot;14&quot;
.AddItem &quot;15&quot;
.AddItem &quot;16&quot;
.AddItem &quot;17&quot;
.AddItem &quot;18&quot;
.AddItem &quot;19&quot;
.AddItem &quot;20&quot;
.AddItem &quot;21&quot;
.AddItem &quot;22&quot;
.AddItem &quot;23&quot;
.AddItem &quot;24&quot;
.AddItem &quot;25&quot;
.AddItem &quot;26&quot;
.AddItem &quot;27&quot;
.AddItem &quot;28&quot;
.AddItem &quot;29&quot;
.AddItem &quot;30&quot;
.AddItem &quot;31&quot;
End With

Call Decide2

Exit Sub
End If
End Sub

Public Sub InsertRows()
'

Dim Message As String

On Error Resume Next

Message = &quot;A Row can not be added here. Select a row that contains merged cells to insert additional rows excluding Category Title Rows.&quot; & vbCrLf & vbCrLf
Message = Message & &quot;This function applies and pertains ONLY to the addition of rows for the Other Category&quot;

If Cells(ActiveCell.Row, 3).MergeCells And Cells(ActiveCell.Row, 3).Locked = False And ActiveCell.Row >= 380 Then

ActiveSheet.Unprotect Password:=&quot;2Tough&quot;
Application.ScreenUpdating = False
Application.EnableEvents = False
Rows(ActiveCell.Row + 1).Select
Selection.Insert Shift:=xlDown
Rows(ActiveCell.Row - 1).Select
Selection.Copy
Rows(ActiveCell.Row + 1).Select
ActiveSheet.Paste
Cells(ActiveCell.Row, 3).Value = &quot;&quot;

Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveSheet.Protect Password:=&quot;2Tough&quot;


Else
MsgBox Message, vbInformation, &quot;Inserting Rows Error&quot;
End If

End Sub

 

[xlbo]

Well it won't compile in xl97 because you are using vbshowmodeless. Whether or not this is called, it is an unknown parameter in xl97 and therefore won't compile.....

Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[TonyJollans]

To compile in 97 you must surround the .Show vbModeless itself with #If / #End If. It is not sufficient to conditionally execute the Call to the routine. So you need to have ...

Public Sub ShowModeless()
#If VBA6 Then
  Insert_Row_Form.Show vbModeless
#End If
Insert_Row_Form.Enabled = True
End Sub

... and similar for Showmodeless2, as well as what you already have.

Enjoy,
Tony