Compilation Error: Only comments may appear after End Sub

arthurbarboza

New Member
Joined
Nov 4, 2014
Messages
3
Hi guys! I'm new here at MrExcel.com but I've done a lot of research about this error that insists to drive me crazy!

I work with hydrological data and now I'm creating a user-friendly sistem that opens the numerous worksheets that have already been developed. The sistem I'm talking about is based on Excel UserForms. Some of them opens the maps, models and etc..

In one of this UserForms, the user can enter the water level of a river station in a TextBox and then press a CommandButton that calls a Private Sub to do the maths and then print the river water flow in a different TextBox. Before that, the user needs to choose a river station (each river station have a particular equation to convert water level to water flow).

So, when I open this Userform and click on the TextBox to type a water level, I get the msg: "Only comments may appear after End Sub...."

I've already checked 100 times but can't find the mistake. Do you guys have a clue what it might be??

The same code, without the "Private Sub UserForm_Initialize()" runs very fine, but I want to avoid that the user click on X button.

The code is:
--------------------------------------------------------------------
Option Explicit


Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
Private Declare PtrSafe Function GetSystemMenu Lib "user32" _
(ByVal hWnd As Long, ByVal bRevert As Long) As LongPtr
Private Declare PtrSafe Function DeleteMenu Lib "user32" _
(ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) _
As LongPtr
Private Const SC_CLOSE As Long = &HF060


Private Sub TbLR_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) 'Check values in Tb.
Dim KeyAscii As MSForms.ReturnInteger
Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Asc("-") 'Permite sinal negativo apenas se for o primeiro caracter.
If InStr(1, Me.TbLR.Text, "-") > 0 Or Me.TbLR.SelStart > 0 Then
KeyAscii = 0
End If
Case Asc(".") 'permite apenas um ponto.
If InStr(1, Me.TbLR.Text, ".") > 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
End Sub
Private Sub cbbFLU_AfterUpdate() 'Clean fieds after changing river station.
tbQ.Value = ""
TbQS.Value = ""
End Sub
Private Sub cbCalcula_Click() 'Do the maths.
Dim estacao As String
Dim LR As Double
Dim Q As Double
Dim QS As Double

Dim a1 As Double
Dim b1 As Double
Dim h0 As Double
Dim a2 As Double
Dim b2 As Double

If cbbFLU.Value = "" Then 'Check if a river station has been selected.
MsgBox "É necessário escolher uma estação fluviométrica.", vbExclamation, "Aviso - Sistema HidroMacaé"
ElseIf TbLR.Value = "" Then 'Check if any value has been typed.
MsgBox "É necessário fornecer um valor de Leitura de Régua.", vbExclamation, "Aviso - Sistema HidroMacaé"
Else

estacao = cbbFLU.Value

If IsNumeric(TbLR.Value) Then 'Check if it is numeric.
LR = TbLR.Value
Else
MsgBox "Fornecer somente valores numéricos no campo indicado.", vbExclamation, "Aviso - Sistema HidroMacaé"
Exit Sub
End If

If estacao = "Macaé de Cima" Then
LR = TbLR.Value
a1 = 13.3455
b1 = 1.9037
h0 = -0.09
If LR < h0 Then 'Confere se é maior que o limite da curva-chave.
MsgBox "Não foi possível realizar o cálculo." & Chr(13) & "Nível d'Água fornecido é muito baixo.", vbExclamation, "Aviso - Sistema HidroMacaé"
Exit Sub
End If
Q = a1 * (LR - h0) ^ b1
tbQ = Round(Q, 2)
MsgBox "Não há curva-chave de sedimentos definida para a estação Macaé de Cima.", , "Aviso - Sistema HidroMacaé"
Else
Select Case estacao
Case "Galdinópolis"
a1 = 6.9868
b1 = 2.2451
h0 = -0.12
a2 = 0.8445
b2 = 2.4833
Case "Piller"
a1 = 7.8458
b1 = 1.992
h0 = -0.05
a2 = 0.3893
b2 = 1.9585
Case "São Romão"
a1 = 74.5771
b1 = 2.3158
h0 = 0.24
a2 = 0.2587
b2 = 2.4705
Case "Barra do Sana"
a1 = 7.004
b1 = 2.2844
h0 = 0.53
a2 = 1.3268
b2 = 2.3241
Case "Ponte do Baião"
a1 = 13.1984
b1 = 1.9785
h0 = 0.52
a2 = 8.2595
b2 = 1.2098
Case "Fazenda Airis"
a1 = 29.125
b1 = 1.7393
h0 = 0.68
a2 = 9.3439
b2 = 1.2705
Case "Jusante BR101"
a1 = 20.6148
b1 = 1.6062
h0 = 0.42
a2 = 0.9494
b2 = 1.6996
Case "Glicério"
a1 = 20.8444
b1 = 2.4403
h0 = 0.64
a2 = 0.641
b2 = 2.1458
Case "São Pedro"
a1 = 11.0702
b1 = 2.0274
h0 = -0.1
a2 = 11.172
b2 = 1.421
End Select
LR = TbLR.Value
If LR < h0 Then 'Confere se é maior que o limite da curva-chave.
MsgBox "Não foi possível realizar o cálculo." & Chr(13) & "Nível d'Água fornecido é muito baixo.", vbExclamation, "Aviso - Sistema HidroMacaé"
tbQ.Value = ""
TbQS.Value = ""
Exit Sub
End If
Q = a1 * (LR - h0) ^ b1
QS = a2 * Q ^ b2


TbLR.Value = Round(LR, 2)
tbQ.Value = Round(Q, 2)
TbQS.Value = Round(QS, 0)
End If
End If
End Sub
Private Sub cbVoltar_Click() 'Close the form.
Me.Hide
' formRedeFLU.Show
End Sub
'Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 'Cancel action of X button, popping a msgbox.
' If CloseMode = vbFormControlMenu Then
' MsgBox "Utilize o botão Voltar.", vbCritical, "Aviso - Sistema HidroMacaé"
' Cancel = True
' End If
'End Sub
Private Sub UserForm_Initialize() 'Avoids clicking in the X button.
Dim hWndForm As Long
Dim hMenu As Long

hWndForm = FindWindow("ThunderDFrame", Me.Caption) 'XL2000
hMenu = GetSystemMenu(hWndForm, 0)
DeleteMenu hMenu, SC_CLOSE, 0&
End Sub
-----------------------------------------------------------

Thanks very much!!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I don't think you've commented out the sub UserForm_QueryClose properly.
 
Upvote 0
Oops, I was looking at the code on my mobile and it appeared that the sub wasn't commented out properly.:eek:

The code actually compiles and runs fine for me after I remove this line.
Code:
Dim KeyAscii As MSForms.ReturnInteger
Do you have any other code in the workbook?
 
Upvote 0
Yes! A lot of them!!!!!!

This Workbook I'm working has just a couple of sheets. The main goal of this workbook is to work as close as possible as an .exe file, by which the user, throw the userforms, will be able to open another sheets, simulate some models, open some images and hiperlinks...

So, to open and close all this forms I need a lot of simples Private Subs.

I've done what you've suggested but didn't work anyway. All userforms of my workbook have a lot of Private Subs in them. Some of them work flawlessly, but some pop up the error msg.. I really don't know what to do.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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