Hi, I'm suggesting that users of a very large spreadsheet use "Data | Forms"
to add or edit data to the sheet. However, for ease of knowing what changes
and additions have been made, I created a macro that highlights the
background color of the cell on the "On change" event of the worksheet. But
it doesn't seem to work when you use the Form. How can I make this happen?
--
THX cs

Maybe you can design your own form and control everything that happens.

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).

And if that's not sufficient, then maybe you could design your own input
userform.

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Cydney wrote:

Hi, I'm suggesting that users of a very large spreadsheet use "Data | Forms"
to add or edit data to the sheet. However, for ease of knowing what changes
and additions have been made, I created a macro that highlights the
background color of the cell on the "On change" event of the worksheet. But
it doesn't seem to work when you use the Form. How can I make this happen?
--
THX cs

--

Dave Peterson

That add-in does work.
And it adheres to the conditions of my macro too.

On the same workbook I have a pivot table with client names and addresses
and their in-house liason. I want the users (liasons) to find their group of
clients in the pivot and be able to add a "yes" to columns directly to the
right of the pivot for invitations to the Events that we're going to be
conducting.

My problem is... When a new pivot selection is made, the "Yes's" don't stay
with the right client name.

I'm sure there's a better way.. but I'm just not seeing it.
--
Thanks. cs


"Dave Peterson" wrote:

Maybe you can design your own form and control everything that happens.

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).

And if that's not sufficient, then maybe you could design your own input
userform.

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Cydney wrote:

Hi, I'm suggesting that users of a very large spreadsheet use "Data | Forms"
to add or edit data to the sheet. However, for ease of knowing what changes
and additions have been made, I created a macro that highlights the
background color of the cell on the "On change" event of the worksheet. But
it doesn't seem to work when you use the Form. How can I make this happen?
--
THX cs

--

Dave Peterson

Maybe you could use a macro to reapply the values--or plop in an =vlookup() to
return the value for each unique key.

Cydney wrote:

That add-in does work.
And it adheres to the conditions of my macro too.

On the same workbook I have a pivot table with client names and addresses
and their in-house liason. I want the users (liasons) to find their group of
clients in the pivot and be able to add a "yes" to columns directly to the
right of the pivot for invitations to the Events that we're going to be
conducting.

My problem is... When a new pivot selection is made, the "Yes's" don't stay
with the right client name.

I'm sure there's a better way.. but I'm just not seeing it.
--
Thanks. cs

"Dave Peterson" wrote:

Maybe you can design your own form and control everything that happens.

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).

And if that's not sufficient, then maybe you could design your own input
userform.

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Cydney wrote:

Hi, I'm suggesting that users of a very large spreadsheet use "Data | Forms"
to add or edit data to the sheet. However, for ease of knowing what changes
and additions have been made, I created a macro that highlights the
background color of the cell on the "On change" event of the worksheet. But
it doesn't seem to work when you use the Form. How can I make this happen?
--
THX cs

--

Dave Peterson


--

Dave Peterson

Hmm. Can you give me an example of how a macro would "reapply" the values?
Or maybe the Vlookup. Those I understand. I'll give that a try.. but I would
also like to entertain the macro option if you'll give me an example.
--
THX cs


"Dave Peterson" wrote:

Maybe you could use a macro to reapply the values--or plop in an =vlookup() to
return the value for each unique key.

Cydney wrote:

That add-in does work.
And it adheres to the conditions of my macro too.

On the same workbook I have a pivot table with client names and addresses
and their in-house liason. I want the users (liasons) to find their group of
clients in the pivot and be able to add a "yes" to columns directly to the
right of the pivot for invitations to the Events that we're going to be
conducting.

My problem is... When a new pivot selection is made, the "Yes's" don't stay
with the right client name.

I'm sure there's a better way.. but I'm just not seeing it.
--
Thanks. cs

"Dave Peterson" wrote:

Maybe you can design your own form and control everything that happens.

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).

And if that's not sufficient, then maybe you could design your own input
userform.

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Cydney wrote:

Hi, I'm suggesting that users of a very large spreadsheet use "Data | Forms"
to add or edit data to the sheet. However, for ease of knowing what changes
and additions have been made, I created a macro that highlights the
background color of the cell on the "On change" event of the worksheet. But
it doesn't seem to work when you use the Form. How can I make this happen?
--
THX cs

--

Dave Peterson


--

Dave Peterson

Say your pt is on sheet999 and the key values are in A3:Axxx and you want to
plop the formulas in G3:Gxxx

Dim LastRow As Long
Dim myRng As Range
Dim Wks As Worksheet

Set Wks = Worksheets("sheet999")
With Wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set myRng = .Range("G3:G" & LastRow)

myRng.Formula = "=if(isna(vlookup(a3,'sheet 2'!a:b,2,0)),""""," _
& "if(vlookup(a3,'sheet 2'!a:b,2,0)="""",""""," _
& "vlookup(a3,'sheet 2'!a:b,2,0)))"
End With




Cydney wrote:

Hmm. Can you give me an example of how a macro would "reapply" the values?
Or maybe the Vlookup. Those I understand. I'll give that a try.. but I would
also like to entertain the macro option if you'll give me an example.
--
THX cs

"Dave Peterson" wrote:

Maybe you could use a macro to reapply the values--or plop in an =vlookup() to
return the value for each unique key.

Cydney wrote:

That add-in does work.
And it adheres to the conditions of my macro too.

On the same workbook I have a pivot table with client names and addresses
and their in-house liason. I want the users (liasons) to find their group of
clients in the pivot and be able to add a "yes" to columns directly to the
right of the pivot for invitations to the Events that we're going to be
conducting.

My problem is... When a new pivot selection is made, the "Yes's" don't stay
with the right client name.

I'm sure there's a better way.. but I'm just not seeing it.
--
Thanks. cs

"Dave Peterson" wrote:

Maybe you can design your own form and control everything that happens.

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).

And if that's not sufficient, then maybe you could design your own input
userform.

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Cydney wrote:

Hi, I'm suggesting that users of a very large spreadsheet use "Data | Forms"
to add or edit data to the sheet. However, for ease of knowing what changes
and additions have been made, I created a macro that highlights the
background color of the cell on the "On change" event of the worksheet. But
it doesn't seem to work when you use the Form. How can I make this happen?
--
THX cs

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Is it possible to "embed" this add-in so people have it pre-installed when I
distribute the Excel workbook?
--
THX cs


"Dave Peterson" wrote:

Maybe you can design your own form and control everything that happens.

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).

And if that's not sufficient, then maybe you could design your own input
userform.

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Cydney wrote:

Hi, I'm suggesting that users of a very large spreadsheet use "Data | Forms"
to add or edit data to the sheet. However, for ease of knowing what changes
and additions have been made, I created a macro that highlights the
background color of the cell on the "On change" event of the worksheet. But
it doesn't seem to work when you use the Form. How can I make this happen?
--
THX cs

--

Dave Peterson

From John's site:

The Enhanced Data Form is free, and is not crippled in any way. It is provided
with no strings attached, no nag messages, and no ads. It can be freely
distributed and used without a license. However, it may not be sold, or included
as part of any other product without the written permission of J-Walk &
Associates.

===
If you bought the source, you could include it in your workbook's project. If
you don't, it looks like you'd have to use separate files.



Cydney wrote:

Is it possible to "embed" this add-in so people have it pre-installed when I
distribute the Excel workbook?
--
THX cs

"Dave Peterson" wrote:

Maybe you can design your own form and control everything that happens.

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).

And if that's not sufficient, then maybe you could design your own input
userform.

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Cydney wrote:

Hi, I'm suggesting that users of a very large spreadsheet use "Data | Forms"
to add or edit data to the sheet. However, for ease of knowing what changes
and additions have been made, I created a macro that highlights the
background color of the cell on the "On change" event of the worksheet. But
it doesn't seem to work when you use the Form. How can I make this happen?
--
THX cs

--

Dave Peterson


--

Dave Peterson

Dave, I have used your example of the worksheet data entry formand just
discovered I cannot use it on my IPAQ, can you advise me if it is possible to
do something similar for a pocket PC ie (Pocket Excel). Your help would be
really very much appreciated, if there is anyway possible I would be most
grateful.

Yours Aye

Bruce

"Dave Peterson" wrote:

Maybe you can design your own form and control everything that happens.

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).

And if that's not sufficient, then maybe you could design your own input
userform.

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Cydney wrote:

Hi, I'm suggesting that users of a very large spreadsheet use "Data | Forms"
to add or edit data to the sheet. However, for ease of knowing what changes
and additions have been made, I created a macro that highlights the
background color of the cell on the "On change" event of the worksheet. But
it doesn't seem to work when you use the Form. How can I make this happen?
--
THX cs

--

Dave Peterson

Sorry, I can't help.

I've never even seen a pocket pc or used pocket excel.

Maybe someone else will chime in or you could find a forum dedicated to those???

piper wrote:

Dave, I have used your example of the worksheet data entry formand just
discovered I cannot use it on my IPAQ, can you advise me if it is possible to
do something similar for a pocket PC ie (Pocket Excel). Your help would be
really very much appreciated, if there is anyway possible I would be most
grateful.

Yours Aye

Bruce

"Dave Peterson" wrote:

Maybe you can design your own form and control everything that happens.

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).

And if that's not sufficient, then maybe you could design your own input
userform.

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Cydney wrote:

Hi, I'm suggesting that users of a very large spreadsheet use "Data | Forms"
to add or edit data to the sheet. However, for ease of knowing what changes
and additions have been made, I created a macro that highlights the
background color of the cell on the "On change" event of the worksheet. But
it doesn't seem to work when you use the Form. How can I make this happen?
--
THX cs

--

Dave Peterson


--

Dave Peterson

Ok thanks anyway, just need to use my laptop!

PS your form is great

B

"Dave Peterson" wrote:

Sorry, I can't help.

I've never even seen a pocket pc or used pocket excel.

Maybe someone else will chime in or you could find a forum dedicated to those???

piper wrote:

Dave, I have used your example of the worksheet data entry formand just
discovered I cannot use it on my IPAQ, can you advise me if it is possible to
do something similar for a pocket PC ie (Pocket Excel). Your help would be
really very much appreciated, if there is anyway possible I would be most
grateful.

Yours Aye

Bruce

"Dave Peterson" wrote:

Maybe you can design your own form and control everything that happens.

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).

And if that's not sufficient, then maybe you could design your own input
userform.

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Cydney wrote:

Hi, I'm suggesting that users of a very large spreadsheet use "Data | Forms"
to add or edit data to the sheet. However, for ease of knowing what changes
and additions have been made, I created a macro that highlights the
background color of the cell on the "On change" event of the worksheet. But
it doesn't seem to work when you use the Form. How can I make this happen?
--
THX cs

--

Dave Peterson


--

Dave Peterson