VBA issue, debug
 

hi, I'm having some trouble with the following VBA on an excel sheet
currently it has 3 check boxes, new, amend, delete which you should b
able to select, before entering in some details.
An error message box checks that the fields are completed and display
relevant error messages.
The work book has several sheet tabs, a front page, a page with th
check boxes on, and a third page where you can enter details, button
hyperlink between the sheet tabs.
Currently if you check the box marked "new" and move sheets you get
runtime error and "object doesn't support this method or property"
It breaks at the line highlighted in pink.

I was just wondering if anyone could help at all.

Thanks in advance! :)

Function sdaCheck()
'SDA
'--------------------------------------------------------------------------------

Dim SignOnErrStr As String
Dim textBoxValue As String
Dim firstName As String
Dim i As Integer
Dim RowNo As Integer
Dim signOn As String
Dim thisChckd As Boolean

'reset text box value
textBoxValue = ActiveWorkbook.Sheets("SDA").txtSDA.Text
thisChckd = False

'NEW:
If textBoxValue = "NEW" Then

'check each cells have values
If ActiveWorkbook.Sheets("SDA").Range("E7") < "" Then
thisChckd = True
ElseIf ActiveWorkbook.Sheets("SDA").Range("G7") < "" Then
thisChckd = True
ElseIf ActiveWorkbook.Sheets("SDA").Range("E8") < "" Then
thisChckd = True
ElseIf ActiveWorkbook.Sheets("SDA").Range("G8") < "" Then
thisChckd = True
Else
End If

'if they all are null then send error else do nothing.
If thisChckd = False Then
SignOnErrStr = SignOnErrStr & ActiveWorkbook.Sheets("Erro
MSGs").Range("C303") & vbCr
Else
End If


*If ActiveWorkbook.Sheets("ACD Logins").txtACD.Text < "" Then
Else*'set row value
RowNo = 14

'set signOn to the sign on field and put is in uppercase
signOn = UCase(ActiveWorkbook.Sheets("SDA").Range("G" & RowNo))

'set the user first name field
firstName = ActiveWorkbook.Sheets("SDA").Range("D" & RowNo)

'reset i
i = 0

For i = 1 To 9

If firstName < "0" Then
If signOn = "" Then
SignOnErrStr = SignOnErrStr & ActiveWorkbook.Sheets("Erro
MSGs").Range("C304") & _
" (SignOn: Row number " & RowNo - 13 & ")" & vbCr
ElseIf signOn = " " Then
SignOnErrStr = SignOnErrStr & ActiveWorkbook.Sheets("Erro
MSGs").Range("C305") & _
" (SignOn: Row number " & RowNo - 13 & ")" & vbCr
ElseIf Not IsNumeric(signOn) Then
SignOnErrStr = SignOnErrStr & ActiveWorkbook.Sheets("Erro
MSGs").Range("C306") & _
" (SignOn: Row number " & RowNo - 13 & ")" & vbCr
ElseIf Not IsNumeric(signOn) Then
Else
End If
End If

'reset the row number, firstName, signOn
RowNo = RowNo + 1
signOn = UCase(ActiveWorkbook.Sheets("SDA").Range("G" & RowNo))
firstName = ActiveWorkbook.Sheets("SDA").Range("D" & RowNo)

Next i

End If

sdaCheck = SignOnErrSt

--
opheli
-----------------------------------------------------------------------
ophelia's Profile: http://www.excelforum.com/member.php...fo&userid=3377
View this thread: http://www.excelforum.com/showthread.php?threadid=56236

VBA issue, debug
 

txtACD isn't a property of the sheets objec

--
Kaa
-----------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...nfo&userid=751
View this thread: http://www.excelforum.com/showthread.php?threadid=56236

VBA issue, debug
 

Try

If ActiveWorkbook.Sheets("ACD Logins").OLEObjects("txtACD").Object.Text <
"" Then

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ophelia" wrote in
message ...

hi, I'm having some trouble with the following VBA on an excel sheet,
currently it has 3 check boxes, new, amend, delete which you should be
able to select, before entering in some details.
An error message box checks that the fields are completed and displays
relevant error messages.
The work book has several sheet tabs, a front page, a page with the
check boxes on, and a third page where you can enter details, buttons
hyperlink between the sheet tabs.
Currently if you check the box marked "new" and move sheets you get a
runtime error and "object doesn't support this method or property"
It breaks at the line highlighted in pink.

I was just wondering if anyone could help at all.

Thanks in advance! :)

Function sdaCheck()
'SDA

'---------------------------------------------------------------------------
-----

Dim SignOnErrStr As String
Dim textBoxValue As String
Dim firstName As String
Dim i As Integer
Dim RowNo As Integer
Dim signOn As String
Dim thisChckd As Boolean

'reset text box value
textBoxValue = ActiveWorkbook.Sheets("SDA").txtSDA.Text
thisChckd = False

'NEW:
If textBoxValue = "NEW" Then

'check each cells have values
If ActiveWorkbook.Sheets("SDA").Range("E7") < "" Then
thisChckd = True
ElseIf ActiveWorkbook.Sheets("SDA").Range("G7") < "" Then
thisChckd = True
ElseIf ActiveWorkbook.Sheets("SDA").Range("E8") < "" Then
thisChckd = True
ElseIf ActiveWorkbook.Sheets("SDA").Range("G8") < "" Then
thisChckd = True
Else
End If

'if they all are null then send error else do nothing.
If thisChckd = False Then
SignOnErrStr = SignOnErrStr & ActiveWorkbook.Sheets("Error
MSGs").Range("C303") & vbCr
Else
End If


*If ActiveWorkbook.Sheets("ACD Logins").txtACD.Text < "" Then
Else*'set row value
RowNo = 14

'set signOn to the sign on field and put is in uppercase
signOn = UCase(ActiveWorkbook.Sheets("SDA").Range("G" & RowNo))

'set the user first name field
firstName = ActiveWorkbook.Sheets("SDA").Range("D" & RowNo)

'reset i
i = 0

For i = 1 To 9

If firstName < "0" Then
If signOn = "" Then
SignOnErrStr = SignOnErrStr & ActiveWorkbook.Sheets("Error
MSGs").Range("C304") & _
" (SignOn: Row number " & RowNo - 13 & ")" & vbCr
ElseIf signOn = " " Then
SignOnErrStr = SignOnErrStr & ActiveWorkbook.Sheets("Error
MSGs").Range("C305") & _
" (SignOn: Row number " & RowNo - 13 & ")" & vbCr
ElseIf Not IsNumeric(signOn) Then
SignOnErrStr = SignOnErrStr & ActiveWorkbook.Sheets("Error
MSGs").Range("C306") & _
" (SignOn: Row number " & RowNo - 13 & ")" & vbCr
ElseIf Not IsNumeric(signOn) Then
Else
End If
End If

'reset the row number, firstName, signOn
RowNo = RowNo + 1
signOn = UCase(ActiveWorkbook.Sheets("SDA").Range("G" & RowNo))
firstName = ActiveWorkbook.Sheets("SDA").Range("D" & RowNo)

Next i

End If

sdaCheck = SignOnErrStr


--
ophelia
------------------------------------------------------------------------
ophelia's Profile:
http://www.excelforum.com/member.php...o&userid=33778
View this thread: http://www.excelforum.com/showthread...hreadid=562368

VBA issue, debug
 

Kaak Wrote:
txtACD isn't a property of the sheets object

got it, brilliant, I think it was a case of not being able to see th
wood for the trees...now sorted :D

thanks

--
opheli
-----------------------------------------------------------------------
ophelia's Profile: http://www.excelforum.com/member.php...fo&userid=3377
View this thread: http://www.excelforum.com/showthread.php?threadid=56236