VBA issue, debug

[ophelia]

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

[Kaak]

txtACD isn't a property of the sheets object

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[ophelia]

txtACD isn't a property of the sheets object

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

thanks!