forms

[David]

I have an excel 2007 work book that requires input from the user in the form of a table. I have created the data input form, and it maps to a tab in the work book, however, resently it has started stopping when updating the data.

The associated code is provided below:

[pre]

Private Sub commentAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("Comments")
Me.txtDate.Value = Format(Date, "Medium Date")

'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a part number
If Trim(Me.txtName.Value) = "" Then
Me.txtName.SetFocus
MsgBox "Please enter your User Name"
Exit Sub
End If

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.txtName.Value
.Cells(lRow, 2).Value = Me.txtDate.Value
.Cells(lRow, 3).Value = Me.ComboBox1.Value
.Cells(lRow, 4).Value = Me.txtComment.Value
.Cells(lRow, 5).Value = Me.TxtSA.Value
End With

'clear the data
Me.txtName.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.ComboBox1.Value = ""
Me.txtComment.Value = ""
Me.txtName.SetFocus

End Sub

[/pre]
The error occurs in the section 'copy the data to the database'


Any assistance would be greatly appreciated.

Plesae note, I am fairly new regarding VBA

 

[Jordan]

Are you able to post an example file of your work? Could you be more specific on exactly what kind of error you've run into?

 

[Luke M]

Has the name of the worksheet "Comments" change possibly? Even adding a space to the end of the name would cause a problem. Or, has that sheet become very hidden or protected?

 

[SirJB7]

Hi, David!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


About your question and additionally to before posts, in what specific statement of the copy section does the error is raised? If first it might be a range issue, else a data issue.


Regards!

 

[David]

The tab "Comments" is confirmed correct, no spaces...


When the error occurs, if i say continue, it continues to paste the data. However, each time the data is saved, the error appears at a different step... sometimes Me.txtDate.Value, sometime on Me.ComboBox1.Value... making tracking the actuall error difficult.


Thanx very much for any assistance you can provide.

 

[David]

error that comes up is:

Code exectuion has been interrupted

When i hit debug, .Cells(lRow, 3).Value = Me.ComboBox1.Value is highlighted in yellow (this time)


Thanx very much for your assistance!!

 

[NARAYANK991]

Hi David ,


I have tried to get the error you mention ; on my PC , this file that I created is working fine.


Do you want to check your macro which runs perfectly OK in this file ?


http://speedy.sh/4cRp7/11a-1.xlsm


Narayan

 

[David]

Narayan,

Thanx very much for the help. Unfortunately, i cannot open this link due to security settings. would you have another methode?


Best Regards,

David

 

[NARAYANK991]

Hi David ,


I can send you this sample file by email ; mine is narayank1026[at]gmail[dot]com


You can mail me so that I get your email address.


Narayan

 

[David]

i have found a solution, which appears to work (so far) Ctrl+Shift+F9.

it seems that there was break somewhere hidden, and from another form Smurf On Spreadsheets, it was suggested...


the strange thing was the error occured in various workbooks, although the coding was the same. I figured it couldn't be a code issue as i did not change all workbooks...


Thanx very much Narayan. This site, and form, has been incredibly helpful over the years...