Why is there a break in code?

When I use:
Activesheet.showdataform

If I enter-in input in the box, the code breaks right after and does not continue as it should, while if I write in nothing, it continues as should.

Why? Any ideas?

I could not replicate the same problem under Excel 2003.

I set up a simple 2-column data set on a worksheet and manually opened the data form from the usual Excel Data manu. Then I used a simple test sub as follows -

Sub Test()
ActiveSheet.ShowDataForm
MsgBox "OK"
End Sub

Whether I used the Close button, or the X button to close the dialog box and either inputting some new values or leaving them blank, the code always continued onto the MsgBox statement.

Has the data form already been set-up under your worksheet (manually or via code) before your ShowDataForm statement is reached?

Do you have any error trapping switched on elsewhere in your code that could re-direct the code elsewhere?

Have you tried stepping through the code one line at a time (Debug - Step Into menu, or F8) to see where the code goes?

THANK YOU FOR THE REPLAY!

I'm not sure what you mean by "Do you have any error trapping switched on elsewhere in your code that could re-direct the code elsewhere?"
But I can tell you that I copy-paste a line before I write showdataform. This line causes the code to call other functions that are in modules... Might this be the problem??

I tried doing step-by-step, and it breaks just after the showdataform... However, if I do not enter-in any input, it continues with the code as should!

I'll pase the code...

Private Sub CommandButton1_Click()
' enters option

Dim sheet As String
Dim sortrange As Range
Worksheets("Buttons").Activate

sheet = ActiveSheet.Range("C4")

If sheet = "Crude_Options" Then
Worksheets(sheet).Activate
ActiveSheet.Unprotect
Application.Rows("2:2").Select
Selection.insert Shift:=xlDown
ActiveSheet.Range("L3:AG3").Select
Selection.copy
ActiveSheet.Range("L2").Select
ActiveSheet.Paste
ActiveSheet.Range("D3").Select
Selection.copy
ActiveSheet.Range("D2").Select
ActiveSheet.Paste
ActiveSheet.ShowDataForm

' auto sort

With Sheets(sheet)
Set sortrange = ActiveSheet.Application.Columns("A:Y")
sortrange.sort Key1:=.Range("D2"), Order1:=xlDescending, key2:=.Range("A2"), key3:=.Range("K2"), Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

Else

Worksheets(sheet).Activate
ActiveSheet.Unprotect
Application.Rows("2:2").Select
Selection.insert Shift:=xlDown
ActiveSheet.Range("K3:AG3").Select
Selection.copy
ActiveSheet.Range("K2").Select
ActiveSheet.Paste
ActiveSheet.Range("B3").Select
Selection.copy
ActiveSheet.Range("B2").Select
ActiveSheet.Paste
ActiveSheet.Range("H3").Select
Selection.copy
ActiveSheet.Range("H2").Select
ActiveSheet.Paste
ActiveSheet.ShowDataForm

' auto sort

With Sheets(sheet)
Set sortrange = ActiveSheet.Application.Columns("A:Y")
sortrange.sort Key1:=.Range("G2"), Order1:=xlDescending, key2:=.Range("J2"), key3:=.Range("B2"), Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End If

ActiveSheet.Protect
Worksheets("positions").Activate
ActiveWorkbook.Save
End Sub

Originally Posted by Loz

I could not replicate the same problem under Excel 2003.

I set up a simple 2-column data set on a worksheet and manually opened the data form from the usual Excel Data manu. Then I used a simple test sub as follows -

Sub Test()
ActiveSheet.ShowDataForm
MsgBox "OK"
End Sub

Whether I used the Close button, or the X button to close the dialog box and either inputting some new values or leaving them blank, the code always continued onto the MsgBox statement.

Has the data form already been set-up under your worksheet (manually or via code) before your ShowDataForm statement is reached?

Do you have any error trapping switched on elsewhere in your code that could re-direct the code elsewhere?

Have you tried stepping through the code one line at a time (Debug - Step Into menu, or F8) to see where the code goes?

THANK YOU FOR THE REPLAY!

I'm not sure what you mean by "Do you have any error trapping switched on elsewhere in your code that could re-direct the code elsewhere?"
But I can tell you that I copy-paste a line before I write showdataform. This line causes the code to call other functions that are in modules... Might this be the problem??

I tried doing step-by-step, and it breaks just after the showdataform... However, if I do not enter-in any input, it continues with the code as should!

I'll pase the code...

Private Sub CommandButton1_Click()
' enters option

Dim sheet As String
Dim sortrange As Range
Worksheets("Buttons").Activate

sheet = ActiveSheet.Range("C4")

If sheet = "Crude_Options" Then
Worksheets(sheet).Activate
ActiveSheet.Unprotect
Application.Rows("2:2").Select
Selection.insert Shift:=xlDown
ActiveSheet.Range("L3:AG3").Select
Selection.copy
ActiveSheet.Range("L2").Select
ActiveSheet.Paste
ActiveSheet.Range("D3").Select
Selection.copy
ActiveSheet.Range("D2").Select
ActiveSheet.Paste
ActiveSheet.ShowDataForm

' auto sort

With Sheets(sheet)
Set sortrange = ActiveSheet.Application.Columns("A:Y")
sortrange.sort Key1:=.Range("D2"), Order1:=xlDescending, key2:=.Range("A2"), key3:=.Range("K2"), Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

Else

Worksheets(sheet).Activate
ActiveSheet.Unprotect
Application.Rows("2:2").Select
Selection.insert Shift:=xlDown
ActiveSheet.Range("K3:AG3").Select
Selection.copy
ActiveSheet.Range("K2").Select
ActiveSheet.Paste
ActiveSheet.Range("B3").Select
Selection.copy
ActiveSheet.Range("B2").Select
ActiveSheet.Paste
ActiveSheet.Range("H3").Select
Selection.copy
ActiveSheet.Range("H2").Select
ActiveSheet.Paste
ActiveSheet.ShowDataForm

' auto sort

With Sheets(sheet)
Set sortrange = ActiveSheet.Application.Columns("A:Y")
sortrange.sort Key1:=.Range("G2"), Order1:=xlDescending, key2:=.Range("J2"), key3:=.Range("B2"), Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End If

ActiveSheet.Protect
Worksheets("positions").Activate
ActiveWorkbook.Save
End Sub

Your code suggests to me that you're inserting a blank row at the top of the data (then copying some columns from the last entry) and expecting the ShowDatForm to ask the user to input the rest of the data for a new blank entry.

It may be that because the data is being shifted about that the ShowDataForm is loosing track of where it is, or it just may not like encountering a blank row. You could try to re-arrange the code so that the ShowDataForm process happens first (& the user inputs the new information by clicking on the NEW button on the ShowDataForm dialog box), and then the rest of the code sorts it etc.

You refer to code in other modules, and I suggest you see if there are any lines that have "On Error" or something similar, as any problems with the CommandButton1_Click subroutine could divert back to any such lines. Try inserting the line On Error Goto 0 at the beginning of the CommandButton1_Click subroutine to trap any problems.