Stupid Input Box Error 2015

[tomn13]

Hey Guys ! The code is so simple (I thought...) but it isn't even displaying the input box, and I've never encountered that. I have some code before the issue to give some variables names:

ActiveWorkbook.Sheets(1).Select
sheett1 = ActiveSheet.Name
ActiveWorkbook.Sheets(2).Select
sheett2 = ActiveSheet.Name
ActiveWorkbook.Sheets(3).Select
sheett3 = ActiveSheet.Name
ActiveWorkbook.Sheets(4).Select
sheett4 = ActiveSheet.Name
ActiveWorkbook.Sheets(5).Select
sheett5 = ActiveSheet.Name
ActiveWorkbook.Sheets(6).Select
sheett6 = ActiveSheet.Name

and then that works fine, but when I try to use:

PrelimOrFinal = Application.InputBox _
             (Prompt:="Which sheet would you like to process? (Pick one of the 'Translated' sheets)" & vbNewLine & _
             "1 - " & sheett1 & vbNewLine & _
             "2 - " & sheett2 & vbNewLine & _
             "3 - " & sheett3 & vbNewLine & _
             "4 - " & sheett4 & vbNewLine & _
             "5 - " & sheett5 & vbNewLine & _
             "6 - " & sheett6 & vbNewLine & _
             vbNewLine & _
             "Please insert a line number that corresponds with a worksheet.", Title:="Which sheet?", Type:=1)
On Error GoTo errnoentry
Application.DisplayAlerts = True

It doesn't display the box and just says that Prelimorfinal = "Error 2015" I'm still really new to this stuff and I thought I would try to break down the code into multiple lines so it's prettier and I undid that as an experiment but that doesn't appear to be the problem.

I'm pretty sure I made a dumb error. But I've tried Googling this and I just have no idea what went wrong.

(The code is posted virtually in entirety, I omitted a line that checks for worksheets.count = 6 at the very top, and I split it up because I'm trying to identify that the top portion works fine and the problem is 'only' in the bottom portion)

[vtxvtx]

i've just tested that code for the input box on my pc and it seems to work fine, could you post the full code maybe?

Andy

[tomn13]

I hate my life. I mean, that like, is practically the full code. Lol. Here:

Sub Translate()

'checks to see if the workbook has the appropriate amount of sheets
If Sheets.Count <> "6" Then GoTo diffsheetserror


'let's get the worksheet names -- because I don't know the object variable for the below section to work properly.
ActiveWorkbook.Sheets(1).Select
sheett1 = ActiveSheet.Name
ActiveWorkbook.Sheets(2).Select
sheett2 = ActiveSheet.Name
ActiveWorkbook.Sheets(3).Select
sheett3 = ActiveSheet.Name
ActiveWorkbook.Sheets(4).Select
sheett4 = ActiveSheet.Name
ActiveWorkbook.Sheets(5).Select
sheett5 = ActiveSheet.Name
ActiveWorkbook.Sheets(6).Select
sheett6 = ActiveSheet.Name



'we gonna ask to see which sheet needs to be translated, users should only pick one of the ones that says original.
prelimorfinal = Application.InputBox _
             (Prompt:="Which sheet would you like to process? (Pick one of the 'Translated' sheets)" & vbNewLine & _
             "1 - " & sheett1 & vbNewLine & _
             "2 - " & sheett2 & vbNewLine & _
             "3 - " & sheett3 & vbNewLine & _
             "4 - " & sheett4 & vbNewLine & _
             "5 - " & sheett5 & vbNewLine & _
             "6 - " & sheett6 & vbNewLine & _
             vbNewLine & _
             "Please insert a line number that corresponds with a worksheet.", Title:="Which sheet?", Type:=1)
On Error GoTo errnoentry
Application.DisplayAlerts = True
'now we have selected which sheet we will translate

'switching to the sheet we selected for translation
ActiveWorkbook.Worksheets(prelimorfinal).Range("A1").Select

'checks for *APPROPRIATE* data in the selected sheet...
lencheck = Application.Len("A1")
If lencheck = "1360" Then GoTo nextline
If lencheck = "38" Then GoTo nextline
If lencheck = "263" Then GoTo nextline
If lencheck = "43" Then GoTo nextline
If lencheck = "19" Then GoTo nextline
GoTo inappropriatedatafromlencheck


nextline:
'Prior to these lines, we need to have selected the correct sheet.
ToDMPfromDOH:
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("A4"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(38, 1), Array(68, 1), Array(76, 1), _
        Array(136, 1), Array(137, 1), Array(145, 1), Array(153, 1), Array(193, 1), Array(204, 1), _
        Array(281, 1), Array(321, 1), Array(361, 1), Array(363, 1), Array(372, 1), Array(382, 1), _
        Array(390, 1), Array(397, 1), Array(403, 1), Array(409, 1), Array(415, 1), Array(423, 1), _
        Array(463, 1), Array(503, 1), Array(543, 1), Array(583, 1), Array(585, 1), Array(594, 1), _
        Array(604, 1), Array(612, 1), Array(652, 1), Array(692, 1), Array(732, 1), Array(772, 1), _
        Array(774, 1), Array(783, 1), Array(793, 1), Array(801, 1), Array(841, 1), Array(881, 1), _
        Array(921, 1), Array(961, 1), Array(963, 1), Array(972, 1), Array(982, 1), Array(990, 1), _
        Array(1030, 1), Array(1070, 1), Array(1110, 1), Array(1150, 1), Array(1152, 1), Array(1161, 1), _
        Array(1171, 1), Array(1179, 1), Array(1219, 1), Array(1259, 1), Array(1299, 1), Array(1339, 1), _
        Array(1341, 1), Array(1350, 1), Array(1360, 1)), TrailingMinusNumbers:=True
GoTo nextline
        
        
ErrorReport:
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("A4"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(7, 1), Array(15, 1), Array(23, 1), _
        Array(26, 1), Array(29, 1), Array(32, 1), Array(35, 1), Array(38, 1)), TrailingMinusNumbers:=True
GoTo nextline
    
Addchange:
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("A4"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(9, 1), Array(17, 1), Array(18, 1), _
        Array(26, 1), Array(34, 1), Array(35, 1), Array(43, 1), Array(51, 1), Array(59, 1), _
        Array(60, 1), Array(61, 1), Array(62, 1), Array(64, 1), Array(72, 1), Array(80, 1), _
        Array(88, 1), Array(96, 1), Array(104, 1), Array(112, 1), Array(152, 1), Array(192, 1), _
        Array(232, 1), Array(234, 1), Array(243, 1), Array(253, 1), Array(263, 1)), TrailingMinusNumbers:=True
GoTo nextline


Deleterecord:
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("A4"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(9, 1), Array(17, 1), Array(25, 1), _
        Array(33, 1), Array(41, 1), Array(42, 1), Array(43, 1)), TrailingMinusNumbers:=True
GoTo nextline

Rejectionrecord:
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("A4"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(9, 1), Array(11, 1), Array(19, 1)), TrailingMinusNumbers:=True
GoTo nextline

End Sub

I'm still writing code so technically the code changes every minute. :P -- oh and I don't anticipate the lower sections working yet, I didn't get a chance to debug them. So please nobody bother correcting that stuff, I probably will be able to. It's just the not-appearing input box that is halting my correction process

[vtxvtx]

okay, for me once i got the code to run (had to change some missing goto lines, but i assumed theyre all fine for you) then the code seemed to run fine. although i got errors on ActiveWorkbook.Worksheets(prelimorfinal).Range("A1").Select where it wouldn't select the cell withotu activating the sheet first (but i didnt think that should be a problem, i miust admit my excel does seem to be playing up a little bit.)

Either way that input box SHOULD work, doesnt seem to be anything wrong with the code.

Have you tried stepping into the code and running it line by line?

Andy

[tomn13]

Ahh sorry about the goto lines. I'm still learning fourm etiquette. I probably need to suck it up and actually post the whole POS -- I just feel bad about posting entire streams of code. I code so poorly that things are usually 10x the length they need to be. So I omitted my "error box" which is where those goto's point. I'm really ghetto. And I think people will laugh at my "error box"

Ok I copied JUST the input box to a new module and took out all other code and it works. It must be a problem with my use of the variables once they were given values...

[tomn13]

I just confirmed the problem is with the inclusion of

ActiveWorkbook.Sheets(1).Select
sheett1 = ActiveSheet.Name
ActiveWorkbook.Sheets(2).Select
sheett2 = ActiveSheet.Name
ActiveWorkbook.Sheets(3).Select
sheett3 = ActiveSheet.Name
ActiveWorkbook.Sheets(4).Select
sheett4 = ActiveSheet.Name
ActiveWorkbook.Sheets(5).Select
sheett5 = ActiveSheet.Name
ActiveWorkbook.Sheets(6).Select
sheett6 = ActiveSheet.Name

... I just want to list the worksheet names in my dialogue box, any ideas on how to do that?

OK I've clearly identified it as a problem with 'list separators' :

"1 - " & sheett1 & vbNewLine & _

-- the way the &'s are used with trying to include the defined worksheet name

[vtxvtx]

That code works for me also, is there anything with any of your sheet names that could cause the problem.

you may also want to adjust the code slightly to

sheett1 = ActiveWorkbook.Sheets(1).Name
sheett2 = ActiveWorkbook.Sheets(2).Name
sheett3 = ActiveWorkbook.Sheets(3).Name
sheett4 = ActiveWorkbook.Sheets(4).Name
sheett5 = ActiveWorkbook.Sheets(5).Name
sheett6 = ActiveWorkbook.Sheets(6).Name

its always best to avoid select staments , however i wouldnt have thought this would fix the code

[tomn13]

UGH! I tried to not use select statements to begin with but I couldn't get the object.property thing correct for assigning sheet names. I replaced the old code with what you wrote, and it works great for defining the variables but I still get the same error as before.

[Norie]

Where exactly do you get the error?

What exactly is the code meant to do?

[tomn13]

The error is that the variable, instead of being defined via the users input into the input box it literally gets defined as "Error 2015" which is USELESS to me.

The code is designed to use the excel's text to columns feature depending on the length of a string size.

[Norie]

The code won't work because Application.Len("A1") will always return 2.

Give this code a try.

It's really just a kind of guess based on the code you posted, I think if we could have a workbook to work with it might help.

Option Explicit

Sub Translate()
Dim rng As Range
Dim prelimorfinal As String
Dim I As Long
Dim arrShtNames

    'checks to see if the workbook has the appropriate amount of sheets
    If Sheets.Count <> 6 Then
        MsgBox "This code is meant for a workbook with 6 sheets."
    End If

    'let's get the worksheet names -- because I don't know the object variable for the below section to work properly.
    ReDim arrShtNames(1 To Sheets.Count)
    For I = 1 To Sheets.Count
        arrShtNames(I) = I & " - " & Sheets(I).Name
    Next I


    'we gonna ask to see which sheet needs to be translated, users should only pick one of the ones that says original.
    prelimorfinal = InputBox _
                    (Prompt:="Which sheet would you like to process? (Pick one of the 'Translated' sheets)" & vbNewLine & _
                             Join(arrShtNames, vbNewLine) & _
                             vbNewLine & _
                             "Please insert a line number that corresponds with a worksheet.", Title:="Which sheet?")

    
    Application.DisplayAlerts = True
    'now we have selected which sheet we will translate

    'switching to the sheet we selected for translation
    Set rng = ActiveWorkbook.Worksheets(Val(prelimorfinal)).Range("A1")

    Select Case Len(rng.Value)

        Case 1360
            rng.TextToColumns Destination:=rng.Offset(3), DataType:=xlFixedWidth, _
                              FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(38, 1), Array(68, 1), Array(76, 1), _
                                               Array(136, 1), Array(137, 1), Array(145, 1), Array(153, 1), Array(193, 1), Array(204, 1), _
                                               Array(281, 1), Array(321, 1), Array(361, 1), Array(363, 1), Array(372, 1), Array(382, 1), _
                                               Array(390, 1), Array(397, 1), Array(403, 1), Array(409, 1), Array(415, 1), Array(423, 1), _
                                               Array(463, 1), Array(503, 1), Array(543, 1), Array(583, 1), Array(585, 1), Array(594, 1), _
                                               Array(604, 1), Array(612, 1), Array(652, 1), Array(692, 1), Array(732, 1), Array(772, 1), _
                                               Array(774, 1), Array(783, 1), Array(793, 1), Array(801, 1), Array(841, 1), Array(881, 1), _
                                               Array(921, 1), Array(961, 1), Array(963, 1), Array(972, 1), Array(982, 1), Array(990, 1), _
                                               Array(1030, 1), Array(1070, 1), Array(1110, 1), Array(1150, 1), Array(1152, 1), Array(1161, 1), _
                                               Array(1171, 1), Array(1179, 1), Array(1219, 1), Array(1259, 1), Array(1299, 1), Array(1339, 1), _
                                               Array(1341, 1), Array(1350, 1), Array(1360, 1))
        Case 38
            rng.TextToColumns Destination:=rng.Offset(3), DataType:=xlFixedWidth, _
                              FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(7, 1), Array(15, 1), Array(23, 1), _
                                               Array(26, 1), Array(29, 1), Array(32, 1), Array(35, 1), Array(38, 1))
        Case 263
            rng.TextToColumns Destination:=rng.Offset(3), DataType:=xlFixedWidth, _
                              FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(9, 1), Array(17, 1), Array(18, 1), _
                                               Array(26, 1), Array(34, 1), Array(35, 1), Array(43, 1), Array(51, 1), Array(59, 1), _
                                               Array(60, 1), Array(61, 1), Array(62, 1), Array(64, 1), Array(72, 1), Array(80, 1), _
                                               Array(88, 1), Array(96, 1), Array(104, 1), Array(112, 1), Array(152, 1), Array(192, 1), _
                                               Array(232, 1), Array(234, 1), Array(243, 1), Array(253, 1), Array(263, 1))
        Case 43
            rng.TextToColumns Destination:=rng.Offset(3), DataType:=xlFixedWidth, _
                              FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(9, 1), Array(17, 1), Array(25, 1), _
                                               Array(33, 1), Array(41, 1), Array(42, 1), Array(43, 1))

        Case 19
            rng.TextToColumns Destination:=rng.Offset(3), DataType:=xlFixedWidth, _
                              FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(9, 1), Array(11, 1), Array(19, 1))
    End Select


End Sub

[tomn13]

OH MY GOD HOW DO YOU PEOPLE GET SO GOOD AT THIS ! lol. Yes. Yes that does precisely what I want it to. But. The problem is. I suck. And I don't understand why it's working.

OK I'll switch out the old crappy code for this newer awesome code and I'll try to learn it thanks so much !

[Norie]

Not sure if it's what you want but this code will go down column A applying the appropriate
text to columns where required.

Sub Translate()
Dim rng As Range
Dim prelimorfinal As String
Dim I As Long
Dim arrShtNames

    'checks to see if the workbook has the appropriate amount of sheets
    If Sheets.Count <> 6 Then
        MsgBox "This code is meant for a workbook with 6 sheets."
    End If

    'let's get the worksheet names -- because I don't know the object variable for the below section to work properly.
    ReDim arrShtNames(1 To Sheets.Count)
    For I = 1 To Sheets.Count
        arrShtNames(I) = I & " - " & Sheets(I).Name
    Next I


    'we gonna ask to see which sheet needs to be translated, users should only pick one of the ones that says original.
    prelimorfinal = InputBox _
                    (Prompt:="Which sheet would you like to process? (Pick one of the 'Translated' sheets)" & vbNewLine & _
                             Join(arrShtNames, vbNewLine) & _
                             vbNewLine & _
                             "Please insert a line number that corresponds with a worksheet.", Title:="Which sheet?")


    Application.DisplayAlerts = True
    'now we have selected which sheet we will translate

    'switching to the sheet we selected for translation

    For I = 1 To Worksheets(Val(prelimorfinal)).Range("A" & Rows.Count).End(xlUp)

        Set rng = ActiveWorkbook.Worksheets(Val(prelimorfinal)).Range("A" & I)

        Select Case Len(rng.Value)

            Case 1360
                rng.TextToColumns Destination:=rng.Offset(3), DataType:=xlFixedWidth, _
                                  FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(38, 1), Array(68, 1), Array(76, 1), _
                                                   Array(136, 1), Array(137, 1), Array(145, 1), Array(153, 1), Array(193, 1), Array(204, 1), _
                                                   Array(281, 1), Array(321, 1), Array(361, 1), Array(363, 1), Array(372, 1), Array(382, 1), _
                                                   Array(390, 1), Array(397, 1), Array(403, 1), Array(409, 1), Array(415, 1), Array(423, 1), _
                                                   Array(463, 1), Array(503, 1), Array(543, 1), Array(583, 1), Array(585, 1), Array(594, 1), _
                                                   Array(604, 1), Array(612, 1), Array(652, 1), Array(692, 1), Array(732, 1), Array(772, 1), _
                                                   Array(774, 1), Array(783, 1), Array(793, 1), Array(801, 1), Array(841, 1), Array(881, 1), _
                                                   Array(921, 1), Array(961, 1), Array(963, 1), Array(972, 1), Array(982, 1), Array(990, 1), _
                                                   Array(1030, 1), Array(1070, 1), Array(1110, 1), Array(1150, 1), Array(1152, 1), Array(1161, 1), _
                                                   Array(1171, 1), Array(1179, 1), Array(1219, 1), Array(1259, 1), Array(1299, 1), Array(1339, 1), _
                                                   Array(1341, 1), Array(1350, 1), Array(1360, 1))
            Case 38
                rng.TextToColumns Destination:=rng.Offset(3), DataType:=xlFixedWidth, _
                                  FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(7, 1), Array(15, 1), Array(23, 1), _
                                                   Array(26, 1), Array(29, 1), Array(32, 1), Array(35, 1), Array(38, 1))
            Case 263
                rng.TextToColumns Destination:=rng.Offset(3), DataType:=xlFixedWidth, _
                                  FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(9, 1), Array(17, 1), Array(18, 1), _
                                                   Array(26, 1), Array(34, 1), Array(35, 1), Array(43, 1), Array(51, 1), Array(59, 1), _
                                                   Array(60, 1), Array(61, 1), Array(62, 1), Array(64, 1), Array(72, 1), Array(80, 1), _
                                                   Array(88, 1), Array(96, 1), Array(104, 1), Array(112, 1), Array(152, 1), Array(192, 1), _
                                                   Array(232, 1), Array(234, 1), Array(243, 1), Array(253, 1), Array(263, 1))
            Case 43
                rng.TextToColumns Destination:=rng.Offset(3), DataType:=xlFixedWidth, _
                                  FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(9, 1), Array(17, 1), Array(25, 1), _
                                                   Array(33, 1), Array(41, 1), Array(42, 1), Array(43, 1))

            Case 19
                rng.TextToColumns Destination:=rng.Offset(3), DataType:=xlFixedWidth, _
                                  FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(9, 1), Array(11, 1), Array(19, 1))
        End Select

    Next I
End Sub

[tomn13]

With the above code I get "Type Mismatch" on this line:

For I = 1 To Worksheets(Val(prelimorfinal)).Range("A" & Rows.Count).End(xlUp)

I tried changing the variable Dim to a couple of things but all was ... fruitless. I'm sure you picked Long for a reason

When I remove Val() it switches to subscript out of range. End xl up has a value of -4162, with rows.count having a value of 1048576. So I have an idea of what that may mean in the above line

[Norie]

What has been entered for prelimorfinal?

[tomn13]

In the input box I put "2" -- without quotes, obviously

And when I hover the mouse over 'prelimorfinal' it does say: "2"

With quotes, which is normal I'm pretty sure.

[Norie]

This is my mistake, and I'm surprised it hasn't happened before.

Anwyay it should be this.

Worksheets(Val(prelimorfinal)).Range("A" & Rows.Count).End(xlUp).Row

[tomn13]

Perfect perfect perfect! The only thing I changed with your code was:

rng.TextToColumns Destination:=rng.Offset(0), DataType:=xlFixedWidth, _

You had 3's there. And it shoved the information three rows below and turned into fajibblejabble. I am so thrilled with your help, this is astounding. !! YAY !! You have made my life so much easier and I've been able to learn a lot from this.

[Norie]

The reason I had a 3 there was because in the original code you were splitting A1 but you'd specified the destination as A4.

I did think it was a bit strange, but it was your code so who am I to say.

PS You don't need the Offset thing at all.