0

Now, I am trying to run my code and suddenly I am getting an error in my Worksheet activation line. Yesterday, it worked well without any problems. Until now I was not able to find the mistake. Please refer to the code below. I'd appreciate if anyone can give me a suggestion on how to run the code without getting an error.

For the below commandbutton I am getting the error when clicked.

Private Sub cmdsubmit_Click()
Dim i As Integer
Dim Submittedtask As String

    Worksheets("Submittedtask").Activate  'this line i am getting error 

    'position cursor in the correct cell A8.
    ActiveSheet.Range("A8").Select
 
    i = 1 'set as the first ID

    'validate first three controls have been entered...
    If Me.txtProject.Text = Empty Then 'Firstname
        MsgBox "Please enter firstname.", vbExclamation
        Me.txtProject.SetFocus 'position cursor to try again
        Exit Sub 'terminate here - why continue?
    End If


    'if all the above are false (OK) then carry on.
    'check to see the next available blank row start at cell A2...
    Do Until ActiveCell.Value = Empty
        ActiveCell.Offset(1, 0).Select 'move down 1 row
        i = i + 1 'keep a count of the ID for later use
    Loop

    'Populate the new data values into the 'Data' worksheet.
    ActiveCell.Value = i 'Next ID number
    ActiveCell.Offset(0, 2).Value = Me.txtProject.Text 'set col B
    ActiveCell.Offset(0, 3).Value = Me.txtEnia.Text 'set col C
    ActiveCell.Offset(0, 1).Value = Me.DTPicker1.Value


    'Clear down the values ready for the next record entry...
    Me.txtProject.Text = Empty
    Me.txtEnia.Text = Empty

    Me.cboLs.Set Focus 'positions the cursor for next record entry
    
    
End Sub
Roy Scheffers
  • 3,832
  • 11
  • 31
  • 36
sant
  • 101
  • 9
  • 1
    What is the specific error message? – Lina Aug 31 '18 at 06:10
  • It may be the `ActiveCell` is off from what you are expecting. You should NOT use ActiveCell unless you are making an AddIn or want to point the end user there. The same as ActiveSheet. – PatricK Aug 31 '18 at 06:12
  • @Patrick in place of ActiveCell can I use '.Cells' – sant Aug 31 '18 at 06:18
  • @Lina there is no specific error as far I see, when click the command button this line " Worksheets("Submittedtask").Activate " is turning to yellow. – sant Aug 31 '18 at 06:19
  • 1
    Is a worksheet named after "Submittedtask" actually there? And what is that `Dim Submittedtask As String` for? – DisplayName Aug 31 '18 at 06:42
  • You should really rework your code and [avoid](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) the use of `.Activate` & `.Select`. Also your current error would only occur if worksheet "Submittedtask" can't be called e.g. doesn't excist. Try use an indexnumber to see if that clears your error. – JvdV Aug 31 '18 at 06:57
  • Yes Submittedtask is the sheet name where i want to transfer my userform data. – sant Aug 31 '18 at 06:58
  • I tried by changing the sheet Name to Sheet1 and run the Code some strange things Happening i.e. after I click on command button(submit) I am getting an error in worksheet line but at the same time if i again running code all my results are transferred to the Excelsheet. – sant Aug 31 '18 at 07:11
  • If your line of code is turning yellow it's not an error - if you press `F5` when that happens does the code continue? Sounds like your code is acting as if there's a break point there. – Darren Bartrup-Cook Aug 31 '18 at 10:01
  • [Excel VBA App stops spontaneously](https://stackoverflow.com/questions/2154699/excel-vba-app-stops-spontaneously-with-message-code-execution-has-been-halted) may help? – Darren Bartrup-Cook Aug 31 '18 at 10:05
  • I am getting a runtime error 9: Subscript out of range. And when I press F5 my code is opening the userform but when I click on Submit button it is showing runtime error. – sant Sep 01 '18 at 11:42
  • I suspect that you have more than one workbook open at the time you run the macro and that the active one is not the one that contains the sheet "submittedtask" – h2so4 Sep 02 '18 at 08:16

0 Answers0