You probably should have left this confusing statement out of your post:
>I cant protect the sheet directly in this case, cos the user feeds thru the
form will not get copied to the database table then.
And you might have gotten the answer you needed the first time.
--
Regards,
Tom Ogilvy
"Matts" wrote:
> Thanks Paul,Dind know it was this simple.
> Thx again
> Matts
>
> "Paul B" wrote:
>
> > Matts, see if this will do what you want,
> >
> > Private Sub cmdAdd_Click()
> >
> > Dim iRow As Long
> > Dim ws As Worksheet
> > Set ws = Worksheets("Data_Dump")
> >
> > 'This is the password for the sheet
> > Const PW As String = "123" '****Change Password Here******
> > 'change "123" to "" if you don't want a password
> >
> > 'Find first empty row in database
> >
> > iRow = ws.Cells(Rows.Count, 1) _
> > .End(xlUp).Offset(1, 0).Row
> >
> > ' Check for incomplete entries before 'Adding Recod'
> >
> > If Trim(Me.CboStaffName.Value) = "" Then
> > Me.CboStaffName.SetFocus
> > MsgBox "Please enter Staff Name"
> > Exit Sub
> > End If
> >
> > 'unprotect the sheet
> > Sheets("Data_Dump").Unprotect Password:=PW
> >
> > ' To Copy the data to the database
> > ws.Cells(iRow, 1).Value = Me.Txtdate.Value
> > ws.Cells(iRow, 2).Value = Me.Txttime.Value
> >
> > 'protects the sheet
> > Sheets("Data_Dump").Protect Password:=PW
> >
> >
> > ' Clear the data
> > Me.Txtdate.Value = Format(Date, "dd-mmm-yy")
> > Me.Txttime.Value = Format(Time, "hh:mm:ss")
> > Me.CboStaffName.SetFocus
> > Me.txtVlu.Visible = False
> > Me.Label7.Visible = False
> > Me.Label8.Visible = False
> > Me.CboICreferer.Visible = False
> >
> > End Sub
> >
> > You may also want to protect the VBA project to keep someone from seeing the
> > password in it,
> >
> > To protect the VBA project, from your workbook right-click the workbook's
> > icon and pick View Code. This icon is at the top-left of the spreadsheet
> > this will open the VBA editor, in Project Explorer right click on your
> > workbook name, if you don't see it press CTRL + r to open the Project
> > Explorer then select VBA project properties, protection, check lock project
> > for viewing and set a password. Press Alt and Q to close this window and go
> > back to your workbook and save and close the file. Be aware that this
> > password can be broken by third party software.
> >
> >
> > --
> > Paul B
> > Always backup your data before trying something new
> > Please post any response to the newsgroups so others can benefit from it
> > Feedback on answers is always appreciated!
> > Using Excel 2002 & 2003
> > "Matts" <(E-Mail Removed)> wrote in message
> > news:F71B0366-16CA-481B-BC2B-(E-Mail Removed)...
> > > Hello Paul,
> > > I'm quite new to excel programming & I'm basically replicating a lot of
> > > ideas/codes as found on numerous sample user forms.
> > >
> > > If what your are advising is the right way to go about it, could you
> > > please
> > > advise on what the code should be & where should it be inserted
> > >
> > > Thx
> > > Matts
> > >
> > > "Paul B" wrote:
> > >
> > >> Matts, if all information in entered when you click the cmdAdd_Click
> > >> button
> > >> how about adding a line of code to unprotect the sheet at the start and
> > >> protect it at the end, would that work?
> > >>
> > >> --
> > >> Paul B
> > >> Always backup your data before trying something new
> > >> Please post any response to the newsgroups so others can benefit from it
> > >> Feedback on answers is always appreciated!
> > >> Using Excel 2002 & 2003
> > >> "Matts" <(E-Mail Removed)> wrote in message
> > >> news:0340C054-FCDE-49B7-AE3F-(E-Mail Removed)...
> > >> > Hi,
> > >> > I've got a user form through which I am accepting various inputs & when
> > >> > I
> > >> > click on the 'add record' button the data gets copied onto rows
> > >> > everytime.
> > >> > Is
> > >> > there anything that i can do to automatically ensure the rows (after
> > >> > updating) gets protected ?
> > >> >
> > >> > I cant protect the sheet directly in this case, cos the user feeds thru
> > >> > the
> > >> > form will not get copied to the database table then.
> > >> >
> > >> > This is how my code currently looks.
> > >> > *****************************
> > >> > Private Sub cmdAdd_Click()
> > >> >
> > >> > Dim iRow As Long
> > >> > Dim ws As Worksheet
> > >> > Set ws = Worksheets("Data_Dump")
> > >> >
> > >> > 'Find first empty row in database
> > >> >
> > >> > iRow = ws.Cells(Rows.Count, 1) _
> > >> > .End(xlUp).Offset(1, 0).Row
> > >> >
> > >> > ' Check for incomplete entries before 'Adding Recod'
> > >> >
> > >> > If Trim(Me.CboStaffName.Value) = "" Then
> > >> > Me.CboStaffName.SetFocus
> > >> > MsgBox "Please enter Staff Name"
> > >> > Exit Sub
> > >> > End If
> > >> >
> > >> > ' To Copy the data to the database
> > >> > ws.Cells(iRow, 1).Value = Me.Txtdate.Value
> > >> > ws.Cells(iRow, 2).Value = Me.Txttime.Value
> > >> >
> > >> > ' Clear the data
> > >> > Me.Txtdate.Value = Format(Date, "dd-mmm-yy")
> > >> > Me.Txttime.Value = Format(Time, "hh:mm:ss")
> > >> > Me.CboStaffName.SetFocus
> > >> > Me.txtVlu.Visible = False
> > >> > Me.Label7.Visible = False
> > >> > Me.Label8.Visible = False
> > >> > Me.CboICreferer.Visible = False
> > >> >
> > >> > End Sub
> > >> > **********************
> > >> > Please help.
> > >> >
> > >> > Thx
> > >> > Matthew
> > >>
> > >>
> > >>
> >
> >
> >
|
Similar Threads
