[Resolved] CreateEventProc Type Mismatch for InputBox
I am trying to insert code into a module in a new workbook. I am able to create the buttons and code without any issue. However I do have an issue with the Inputbox when it is being placed in the module. I get a Type Mismatch error. I have isolated the error to that line by debugging. I included the entire snippet just so it can be seen by those who would help me. I have tried to set the input type to "2" (string) as it still shows. I also have tried to change the string strAns to a variant (which I never do) in order to handle whatever it decided it wanted to be. I am stumped. I appreciate any help.
Code:
' write button code
With ActiveWorkbook.VBProject.VBComponents("Sheet" & intSheets + 2).CodeModule
lngStartLine = .CreateEventProc("Click", strBttnName) + 1
.InsertLines lngStartLine, _
"Dim strAns as String: 'strAns = """"" & vbCrLf & _
"Dim strBD As String: strBD = Worksheets(""WIP Main"").Range(""ContractSignedDate"").Value" & vbCrLf & _
"Dim strED As String: strED = Worksheets(""WIP Main"").Range(""PlannedShipDate"").Value" & vbCrLf & vbCrLf & _
"strAns = InputBox(""Enter date of completion"" & vbCrLf & ""("" & strBD & " - " & strED & "")"", ""Enter Date"", FormatDateTime(Now(), vbShortDate), Type:=2)" & vbCrLf & _
"If strAns = """" Then" & vbCrLf & _
" Exit Sub" & vbCrLf & _
"ElseIf fFillDate(CDate(strAns)) = False Then" & vbCrLf & _
" Exit Sub" & vbCrLf & _
"End If" & vbCrLf & _
"Application.EnableEvents = False" & vbCrLf & _
"Range(""I" & Worksheets(strArrSheets(intSheets)).Range("A" & intX + 1).Value & "ED" & Left$(strArrSheets(intSheets), 3) & """" & ").Value = strAns" & vbCrLf & _
"Range(""I" & Worksheets(strArrSheets(intSheets)).Range("A" & intX + 1).Value & "Per" & Left$(strArrSheets(intSheets), 3) & """" & ").Value = 1" & vbCrLf & _
"Application.EnableEvents = True" & vbCrLf
End With
Thanks in advance. I have learned all I know from this forum. This place rocks!
Re: CreateEventProc Type Mismatch for InputBox
Well, I figured it out on my own. Looks like I missed the single quotes...
I will post it in case anyone wants to look into some CreateEventProc. There doesn't seem to be much out there on the topic.
Code:
' write button code
With ActiveWorkbook.VBProject.VBComponents("Sheet" & intSheets + 2).CodeModule
lngStartLine = .CreateEventProc("Click", strBttnName) + 1
.InsertLines lngStartLine, _
"Dim strAns as Variant: 'strAns = """"" & vbCrLf & _
"Dim strBD As String: strBD = Worksheets(""WIP Main"").Range(""ContractSignedDate"").Value" & vbCrLf & _
"Dim strED As String: strED = Worksheets(""WIP Main"").Range(""PlannedShipDate"").Value" & vbCrLf & vbCrLf & _
"strAns = InputBox(""Enter date of completion"" & vbCrLf & ""("" & strBD & "" - "" & strED & "")"", ""Enter Date"", FormatDateTime(Now(), vbShortDate))" & vbCrLf & _
"If strAns = """" Then" & vbCrLf & _
" Exit Sub" & vbCrLf & _
"ElseIf fFillDate(CDate(strAns)) = False Then" & vbCrLf & _
" Exit Sub" & vbCrLf & _
"End If" & vbCrLf & _
"Application.EnableEvents = False" & vbCrLf & _
"Range(""I" & Worksheets(strArrSheets(intSheets)).Range("A" & intX + 1).Value & "ED" & Left$(strArrSheets(intSheets), 3) & """" & ").Value = strAns" & vbCrLf & _
"Range(""I" & Worksheets(strArrSheets(intSheets)).Range("A" & intX + 1).Value & "Per" & Left$(strArrSheets(intSheets), 3) & """" & ").Value = 1" & vbCrLf & _
"Application.EnableEvents = True" & vbCrLf
End With
