Edit Records

[vmjan02]

Hi,

I have a set of records from a4:J3993.
Is there a way or code as the user cannot change or add any of the records.

But I don't want the to protect and un-protect the excel sheet.

Thanks.

 

[mikerickson]

Put them on a sheet and then hide (or very hide) the sheet.
Use formulas to refer to those cells. If you want the user to see the records use formulas like =HiddenSheet!A4 and then drag to duplicate the records on another sheet.

 

[vmjan02]

Well I have set of codes running as
in UserForm1 I have combox1

Code
Private Sub UserForm_Initialize()
Range("C5", Range("C" & Rows.Count).End(xlUp)).Name = "NodeName"
Me.ComboBox1.RowSource = "NodeName"
CommandButton2.Visible = False
Label2.Visible = False
TextBox1.Visible = False
End Sub

and in userform1 I have other code, were it will email the data.

If I do it veryhidden then there is problem.

So just need a code were it will not protect then sheet, but will not allow the user to edit or enter the data.

I have this code below but its not working.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Range("A4:J3993), Target) Is Nothing Then
Application.EnableEvents = False
Application.EnableEvents = True
End If
End Sub

 

[mikerickson]

I would change that code to reference the sheet, so you aren't required to have the data sheet active, and to eliminate the use of .RowSource

Private Sub UserForm_Initialize()
     With ThisWorkbook.Sheets("HiddenSheet").Range("C5", Range("C" & Rows.Count).End(xlUp))
         Me.ComboBox1.List = Application.Transpose(.Value)
    End With
    CommandButton2.Visible = False
    Label2.Visible = False
    TextBox1.Visible = False
End Sub