Invalid Cast Exception

[ImSimple]

I am trying to convert a perfectly good VBA code to Visual Basic using Visual Studio 2008 Express and I am getting an Invalid Cast Exception.

Any help is extraordinarily appreciated. I'm about worn out with this project.

Here's my code:

excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Add("F:\Work Stuff\Scope Sheet Creator\Scope Sheet Template.xlt")
excel.Visible = True
wb.Activate()

'Dim chbx As CheckBox
'For Each chbx In Me.Controls 'too infinite must fix
Dim chbxArray(2) As CheckBox
Dim chbx As CheckBox
For Each chbx In Me.Controls
If chbx.Checked = True Then
wb.Sheets("Template").activate()
wb.Sheets("Template").select()
wb.Sheets("Template").copy(after:=sheets.Count, Name:="chbx.text")

End If
Next
excel = Nothing
wb = Nothing
End Sub

Jacque

 

[RiverGuy]

which line of code?

 

[DaveInIowa]

For Each chbx In Me.Controls

What if a control in Me.Controls isn't a CheckBox?

 

[DaveInIowa]

For Each ctrl As Control In Me.Controls
    If (TypeOf ctrl Is CheckBox AndAlso CType(ctrl, CheckBox).Checked) Then
        '...
    End If
Next

 

[Andrzejek]

Dave,

This code will pick only checkboxes that are on the Form itslf, it will not detect checked boxes that are in another containers, like group boxes. Right?

Have fun.

---- Andy

 

[ImSimple]

WOW! Thanks everyone. Dave, I started to use that but got side tracked. i tried it but now i am getting an error at this line

wb.Sheets("Template").copy(after:=sheets.Count, Name:="chbx.text")

Jacque

 

[DaveInIowa]

This was "perfectly good VBA code" using an Integer value for the "After" parameter and a non-existent "Name" parameter in the Copy method?

 

[ImSimple]

'BACK TO TOP
Private Sub BackToTop1_Click()
ScrollBar1.Value = 0
End Sub
Private Sub CommandButton1_Click()
Dim lngIndex As Long
Dim blnArray(75) As Boolean

'CREATE THE SHEETS
For lngIndex = 1 To 75
If ScopeSheetCreator.Controls("CXSheet" & lngIndex).Value = True Then
Sheets("Template Sheet").Select
Sheets("Template Sheet").Copy After:=Sheets(Sheets.Count)
Sheets("Template Sheet (2)").Select
Sheets("Template Sheet (2)").Name = ScopeSheetCreator.Controls("TXSheet" & lngIndex).Value
ActiveSheet.Range("D1").Select

'COMPANY NAME
If CBKillian.Value = True Then
ActiveCell.Value = "Killian Construction Co."
Else
If CBKCC.Value = True Then
ActiveCell.Value = "KCC Contractor Inc."
Else
If CBWestern.Value = True Then
ActiveCell.Value = "Killian Western LLC"
Else
If CBEastern.Value = True Then
ActiveCell.Value = "Killian Eastern LLC"
End If
End If
End If
End If

'PROJECT NAME & BID DATE
ActiveSheet.Range("H6").Select
ActiveCell.Value = TBProjectName.Value
ActiveSheet.Range("H7").Select
ActiveCell.Value = TBBidDate.Value
ActiveSheet.Range("E31").Select

'GENERAL ITEMS
Dim genIndex As Long
Dim genArray(20) As Boolean
For genIndex = 1 To 20
If ScopeSheetCreator.Controls("CXGenItem" & genIndex).Value = True Then
ActiveCell.Value = ScopeSheetCreator.Controls("TXGenItem" & genIndex).Value
ActiveCell.Offset(1, 0).Select
End If
Next

'HEADINGS
Dim head1Index As Long
Dim head1Array(12) As Boolean
For head1Index = 1 To 12
If ScopeSheetCreator.Controls("CXHead" & lngIndex & "_" & head1Index).Value = True Then
ActiveCell.Value = ScopeSheetCreator.Controls("TXHead" & lngIndex & "_" & head1Index).Value
ActiveCell.Offset(1, 0).Select

'ITEMS
'If items are not the same under each heading error occurs
'script tries to find next item up to count before it will go on to the next heading, sheet

Dim itmIndex As Long
Dim itmArray(65)
For itmIndex = 1 To 65
If ScopeSheetCreator.Controls("CXItem" & lngIndex & "_" & head1Index & "_" & itmIndex).Value = True Then
ActiveCell.Value = ScopeSheetCreator.Controls("TXItem" & lngIndex & "_" & head1Index & "_" & itmIndex).Value
ActiveCell.Offset(1, 0).Select

On Error Resume Next

End If 'Items
Next

On Error Resume Next
End If 'Headings
Next

On Error Resume Next
End If 'SHEETS
Next 'SHEETS
End Sub
'LOAD FORM
Private Sub UserForm_Initialize()
'SCROLL BAR
With ScopeSheetCreator
Label1.Move ((.InsideWidth - Label1.Width) / 2), ((.InsideHeight - Label1.Height) / 2)
.ScrollBars = fmScrollBarsBoth
.ScrollHeight = .InsideHeight * 7
.ScrollWidth = .InsideWidth * 2
End With

End Sub

Jacque

 

[ImSimple]

Only in the end there will be 75 sheets and the headings and items are no longer checkboxes because while the code was perfectly good, I was dealing with a form that needed 2080 controls and that wasn't good so now the items will be in multi select list boxes and only the sheets will be checkboxes.

Jacque

 

[ImSimple]

I thought the "After" and "Name" parameters would be fine since I added the Microsoft Excel Object Library to the project references.

Jacque