UserForm not entering data in to spreadsheet

[LoneWolf3574]

First off let me state that before this spreadsheet, I never used Macros whatsoever, so I’m completely new to it. I did use the “New to Excel” Sticky and a knowledgeable friend to get where I am now but have reached an impasse as to the why.

My first question is how do I change the coding to stop the following Visual Basic error when I click add – “Run-time error ‘1004’: The cell or chart that you are trying to change is protected and therefore read-only…”? I created a Macro that unlocks all affected sheets as a temporary workaround, but this UserForm will be for everyone to use where I work and I don’t want them using it.

My second question is how do I get the UserForm to actually enter data? I can use the form, but it doesn’t actually enter any data into my table (a little frustrating).

I’ve cut down everything to the objects, macros and sheets in question and attached it. Any help would be greatly appreciated at this point. Thank you all in advance.

UserThermalsForm.xlsm

[Sindhus]

In order to enter the data into your database, you can use me.textboxname.text instead of .value.

And for the run time error, instead of protecting the sheet, try to hide the sheet and use protect workbook option. The users cannot unhide the worksheet, when the workbook is protected.

[LoneWolf3574]

The "me.textboxname.text" instead of .value didn't work . As far as unprotecting the worksheet so as to enter data, I changed a couple lines of code in the opening of the user form and the close button on the form. I did find that by default the workbook can't be manipulated with the Userform window open, so nothing to change/protect there. I need for anybody to look at the table at any time, a coworker suggested eventually making it searchable, to which I said one headache at a time .

Sub Rectangle1_Click()
    Sheets("Thermals").Select
    ActiveSheet.Unprotect Password:="thermals014"
    Sheets("Main").Select
frmThermals.Show
End Sub

I added/modified/pasted this at the end of the Userform code

Private Sub cmdClose_Click()
  Sheets("Thermals").Select
  ActiveSheet.Protect Password:="thermals014", DrawingObjects:=True, Contents:=True, Scenarios:=True
  Sheets("Main").Select
  Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the button!"
  End If
End Sub

[LoneWolf3574]

I just alt-end the data table and found that the data IS being entered after line 365, but it's not being entered into the correct area on the data table.

[Sindhus]

Hi,
Do you still have trouble in your code? Your new code seems to be working.

Regards,
Sindhu

[LoneWolf3574]

The only thing I'm still having an issue with is the data table, the Userform works, but it starts with line 365, which is not where my data starts. The only thing I can think of at the moment is to create two more text boxes with the date and time. That may be better in the long run if somebody gets confused or forgets to make the data entries for the day, then we would know which is missing. I still would have the same issue as I have a vlookup function related to the data table, making the first "empty" data row still 365 right now.

[Sindhus]

If you want to find last used row in A column, you can try Range("A65536").End(xlup).row

[LoneWolf3574]

If you want to find last used row in A column, you can try Range("A65536").End(xlup).row

I just now tried that changing to B column and get a "Compile error: Invalid use of property" with the ".Row" highlighted in debug. It's progress, better than the other results I've been getting .

I've also the following, but neither worked

Range("D5:P364").End(xlDown).Offset(1, 0).Select

Range("D5").End(xlDown).Offset(1, 0).Range("A").Select

I've included an attachment of the progress so far.

UserThermalsForm.xlsm

[Sindhus]

It should be

 iRow = Worksheets("Thermal").Range("B65535").End(xlup).Row

[LoneWolf3574]

That did the trick, just added a " + 1" to the end, otherwise it overwrites the last line in the table. Now I've got other problems to work on and other questions to ask. Thank you ever so much for your help, I really do appreciate it.