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!!
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!!