Extended Worksheet data entry form -Debra Dalgleish/Dave Peterson pls help

[kev]

Hi Dave,

I came across your worksheet data entry form, adapted the idea for my
project and it worked perfectly fine.
Right now, i received a feedback from my team asking whether an edit
feature can be incorporated to this worksheet.I suggested them to do
the editing direct to the cell but they were hoping there will be a
macro solution.

Is there a way that we can call back the entire row (the one that needs
to be edited) of data in parts data sheet, have them appear in a new
sheet and make the changes there. Plus there is an additional criteria
whereby we only want to allow them to change 2 columns namely x, y the
rest of data will be locked(appear disabled).
Assuming each row we are gonna place a unique ID, can this be used to
call back the data?

my next question will be: i have placed a new sheet called Pivot table
which will take data range from the parts database. whenever i input
something new and save it, the changes is not reflected automatically
in the pivot table unless i click refresh data. i even set the option:
refresh on open but this does not work as the workbook is already open
and the changes was made later.

there is another option which is refresh on intervals but i was told
that this is for external data whereby mine all lies in the same
workbook. But then, it seems like i cannot even click tat option since
it is disabled i dunno why.

I know there seems to be quite a number of requests in here but i
really am looking forward for your help.
this is my final project for the quarter and i would like to impress
them...


Thanks in advance.

 

[Dave Peterson]

The easiest one first.

You can use a worksheet event to refresh the pivottable each time you change to
that sheet.

Option Explicit
Private Sub Worksheet_Activate()
Dim myPT As PivotTable
Application.EnableEvents = False
For Each myPT In Me.PivotTables
myPT.RefreshTable
Next myPT
Application.EnableEvents = True
End Sub

This kind of code goes behind the worksheet with the pivottable.

======
As for the more difficult question...

It may be time to actually use a userform that gets input from the user.

Instead of designing your own form, you may find using Data|Form easier.

And if that isn't enough, John Walkenbach has an enhanced data form:
http://j-walk.com/ss/dataform/index.htm

And the source code is available for a small fee ($20 USA, IIRC).

 

[Earl Kiosterud]

Dave's not here, man.

 

[Earl Kiosterud]

Oh. There your are, man.

 

[kev]

HI Dave,

Thanks for a quick feedback.
Just another query, is it possible to add another field called
autonumber in the "Input" sheet. Asumming my worksheet gonna start with
zero records. This autonumber field will have the capability of
incrementing each time new records come in. I do not know how to
achieve this or whether it is possible. fyi, i intend to lock the
'PartsData" sheet from the user view, thus when the user input new
record all they need to know is the autonumber( so when they need
editing, they will reference it by autonumber to the super users who
will be in charge of editing)

i read several topics in this forum but just couldnt find what i need.

Hope you could help or suggest a new method.

p/s: it may seem very much bugging but you are my only hope..sorry and
thanks a lot for all d feedback which was of great help to me..

 

[Dave Peterson]

Shift everything over one column on the parts data worksheet.
Format Column A to General (so it won't look like a time/date)

And put this code in:

With historyWks
.Cells(nextRow, "A").Value = nextRow - 1
With .Cells(nextRow, "B")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "C").Value = Application.UserName
oCol = 4
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

It replaces the code that looks very close to this.
(Replace, not in addition to.)