Debug error 13

Welcome to the forum.

I know I need to add the third worksheet on a pop-up ...

Pardon?

That confused me too. What code actually triggers the error though?

The error is triggered when the third worksheet is referenced. If I coment out the third Gosub the macro works. When I added the second sheet I found a way to add a worksheet but I can't remember it or find it now . The way included selecting something in the project window and typing a Cntl and another key. This brought up a window that allowed me to add the worksheet.

I had to shorten my code to include it here:

Sub PokeCardNumbers()
    Dim channelNumber
    Dim sheetNumber
    Dim rangeToPoke
    
    channelNumber = Application.DDEInitiate("MBENET", "PLC1")
    sheetNumber = 1
    GoSub WriteData
    Application.DDETerminate channelNumber

    channelNumber = Application.DDEInitiate("MBENET", "PLC2")
    sheetNumber = 3
    GoSub WriteData
    Application.DDETerminate channelNumber
    
    channelNumber = Application.DDEInitiate("MBENET", "PLC3")
    sheetNumber = 5
    GoSub WriteData
    Application.DDETerminate channelNumber
    Exit Sub
    
WriteData:
    Set rangeToPoke = ThisWorkbook.Worksheets(sheetNumber).Range("E3")
    If rangeToPoke.Value <> 65535 Then
        Application.DDEPoke channelNumber, "40001", rangeToPoke
        rangeToPoke.Value = 65535
    End If
    Return

End Sub

You shouldn't get a type mismatch error if the sheet doesn't exist, you should get a subscript out of range. However, you are looking for sheets 1, 3 and 5 so you'll need more than 3 sheets anyway. Can't you just add them through the user interface by clicking the new sheet button?
I have no idea why you are using GoSub either which is somewhat outdated and makes for unnecessarily convoluted code.

You still didn't say which line actually triggers the error. If it's this one:

If rangeToPoke.Value <> 65535 Then

then you probably have an error value in that cell.

Bill,

Please take a few minutes to read the forum rules, and then edit post#5 to add code tags.

If rangeToPoke.Value <> 65535 Then

See my last post then - sounds like there is an error in that cell.

There is an error in the rangetopoke. How can I clear it? Worksheet 5 is in the Workbook, the macro doesn't acknowledge it.

You could add an error handler to the code - for example:

If IsError(rangeToPoke.Value) Then rangetopoke.ClearContents
If rangeToPoke.Value <> 65535 Then
        Application.DDEPoke channelNumber, "40001", rangeToPoke
        rangeToPoke.Value = 65535

No, it's not. It's due to there being an error value in that cell, otherwise the error would not occur on that line.

If, as you said, this line is causing a type mismatch error:

If rangeToPoke.Value <> 65535 Then

then the value can't be 65535. Are you sure you are checking the right sheet? What do you get if you add this line before that one:

MsgBox rangeToPoke.Value & ": " & rangeToPoke.Text

So did you add the error check I suggested?

No problem - glad you got it fixed!