I would like some of my files to automatically generate a value in the File
Property field "Category", based on the contents of a specified cell (eg.
A1). If it is possible to do this, how would one go about it?

Pastel, you'll need an event procedure. Right-click on the sheet tab
of the worksheet you want. Select View Code. Insert this code. This
is just a sample, of course, but should get you started. James

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveWorkbook
If [a1] = 1 Then
.BuiltinDocumentProperties("Category") = "One"
ElseIf [a1] = 2 Then
.BuiltinDocumentProperties("Category") = "Two"
End If
End With
End Sub


Pastel Hughes wrote:
I would like some of my files to automatically generate a value in the File
Property field "Category", based on the contents of a specified cell (eg.
A1). If it is possible to do this, how would one go about it?

James,

You'll have to forgive me, as I am a novice to VB for Excel.
This works ... sort of. It produces the text in the Category field,
however, there is a formula in A1 that changes according to a number of
variables.
The category text does not change when the result of this formula changes,
unless I copy the formula over itself. Do you have any idea how I might get
around this?

Thanks again

"Zone" wrote:

Pastel, you'll need an event procedure. Right-click on the sheet tab
of the worksheet you want. Select View Code. Insert this code. This
is just a sample, of course, but should get you started. James

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveWorkbook
If [a1] = 1 Then
.BuiltinDocumentProperties("Category") = "One"
ElseIf [a1] = 2 Then
.BuiltinDocumentProperties("Category") = "Two"
End If
End With
End Sub


Pastel Hughes wrote:
I would like some of my files to automatically generate a value in the File
Property field "Category", based on the contents of a specified cell (eg.
A1). If it is possible to do this, how would one go about it?

Pastel,
No problem. This event fires whenever any change is made to the
worksheet. I presumed you would be making changes to the worksheet, so
that's what I set the event procedure to capture. What is the formula
in A1? We'll try agin.
James

Pastel Hughes wrote:
James,

You'll have to forgive me, as I am a novice to VB for Excel.
This works ... sort of. It produces the text in the Category field,
however, there is a formula in A1 that changes according to a number of
variables.
The category text does not change when the result of this formula changes,
unless I copy the formula over itself. Do you have any idea how I might get
around this?

Thanks again

"Zone" wrote:

Pastel, you'll need an event procedure. Right-click on the sheet tab
of the worksheet you want. Select View Code. Insert this code. This
is just a sample, of course, but should get you started. James

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveWorkbook
If [a1] = 1 Then
.BuiltinDocumentProperties("Category") = "One"
ElseIf [a1] = 2 Then
.BuiltinDocumentProperties("Category") = "Two"
End If
End With
End Sub


Pastel Hughes wrote:
I would like some of my files to automatically generate a value in the File
Property field "Category", based on the contents of a specified cell (eg.
A1). If it is possible to do this, how would one go about it?

James,

The formula contains links to two other sheets.
All of the direct changes take place in the linked sheets.
If the event fires only for direct changes to the active sheet,
this could explain why the property does not get updated.
I guess this would require some additional script in the VB module.
The trouble is, I don't know what that would be.
Thanks for staying with me.


"Zone" wrote:

Pastel,
No problem. This event fires whenever any change is made to the
worksheet. I presumed you would be making changes to the worksheet, so
that's what I set the event procedure to capture. What is the formula
in A1? We'll try agin.
James

Pastel Hughes wrote:
James,

You'll have to forgive me, as I am a novice to VB for Excel.
This works ... sort of. It produces the text in the Category field,
however, there is a formula in A1 that changes according to a number of
variables.
The category text does not change when the result of this formula changes,
unless I copy the formula over itself. Do you have any idea how I might get
around this?

Thanks again

"Zone" wrote:

Pastel, you'll need an event procedure. Right-click on the sheet tab
of the worksheet you want. Select View Code. Insert this code. This
is just a sample, of course, but should get you started. James

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveWorkbook
If [a1] = 1 Then
.BuiltinDocumentProperties("Category") = "One"
ElseIf [a1] = 2 Then
.BuiltinDocumentProperties("Category") = "Two"
End If
End With
End Sub


Pastel Hughes wrote:
I would like some of my files to automatically generate a value in the File
Property field "Category", based on the contents of a specified cell (eg.
A1). If it is possible to do this, how would one go about it?

Pastel,
You're welcome. Still here. I'm not the largest brain in here by
any means, but I try to help people who have issues I have run into,
especially if the large brains don't answer right away. I believe your
assessment of the situation, namely that since the change to the sheet
came from an external source, A1's formula hasn't changed, so Excel
considers that no change has occurred, event-wise. Nevertheless, since
A1's VALUE has changed, in reality a change has occurred to the sheet.
We should be able to detect this change. If you have not changed
Excel's calculation mode from the default setting (automatic), a change
in A1's value should trigger a recalculation of the sheet. Therefore,
change the first line of your code to look like this:

Private Sub Worksheet_Calculate()

and we'll try to trigger on the change to A1's value. Let me know how
this works! James

Pastel Hughes wrote:
James,

The formula contains links to two other sheets.
All of the direct changes take place in the linked sheets.
If the event fires only for direct changes to the active sheet,
this could explain why the property does not get updated.
I guess this would require some additional script in the VB module.
The trouble is, I don't know what that would be.
Thanks for staying with me.


"Zone" wrote:

Pastel,
No problem. This event fires whenever any change is made to the
worksheet. I presumed you would be making changes to the worksheet, so
that's what I set the event procedure to capture. What is the formula
in A1? We'll try agin.
James

Pastel Hughes wrote:
James,

You'll have to forgive me, as I am a novice to VB for Excel.
This works ... sort of. It produces the text in the Category field,
however, there is a formula in A1 that changes according to a number of
variables.
The category text does not change when the result of this formula changes,
unless I copy the formula over itself. Do you have any idea how I might get
around this?

Thanks again

"Zone" wrote:

Pastel, you'll need an event procedure. Right-click on the sheet tab
of the worksheet you want. Select View Code. Insert this code. This
is just a sample, of course, but should get you started. James

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveWorkbook
If [a1] = 1 Then
.BuiltinDocumentProperties("Category") = "One"
ElseIf [a1] = 2 Then
.BuiltinDocumentProperties("Category") = "Two"
End If
End With
End Sub


Pastel Hughes wrote:
I would like some of my files to automatically generate a value in the File
Property field "Category", based on the contents of a specified cell (eg.
A1). If it is possible to do this, how would one go about it?

James,

Bingo! This works perfectly.
Now I owe you one.
But how to pay?

Thanks again!

"Zone" wrote:

Pastel,
You're welcome. Still here. I'm not the largest brain in here by
any means, but I try to help people who have issues I have run into,
especially if the large brains don't answer right away. I believe your
assessment of the situation, namely that since the change to the sheet
came from an external source, A1's formula hasn't changed, so Excel
considers that no change has occurred, event-wise. Nevertheless, since
A1's VALUE has changed, in reality a change has occurred to the sheet.
We should be able to detect this change. If you have not changed
Excel's calculation mode from the default setting (automatic), a change
in A1's value should trigger a recalculation of the sheet. Therefore,
change the first line of your code to look like this:

Private Sub Worksheet_Calculate()

and we'll try to trigger on the change to A1's value. Let me know how
this works! James

Pastel Hughes wrote:
James,

The formula contains links to two other sheets.
All of the direct changes take place in the linked sheets.
If the event fires only for direct changes to the active sheet,
this could explain why the property does not get updated.
I guess this would require some additional script in the VB module.
The trouble is, I don't know what that would be.
Thanks for staying with me.


"Zone" wrote:

Pastel,
No problem. This event fires whenever any change is made to the
worksheet. I presumed you would be making changes to the worksheet, so
that's what I set the event procedure to capture. What is the formula
in A1? We'll try agin.
James

Pastel Hughes wrote:
James,

You'll have to forgive me, as I am a novice to VB for Excel.
This works ... sort of. It produces the text in the Category field,
however, there is a formula in A1 that changes according to a number of
variables.
The category text does not change when the result of this formula changes,
unless I copy the formula over itself. Do you have any idea how I might get
around this?

Thanks again

"Zone" wrote:

Pastel, you'll need an event procedure. Right-click on the sheet tab
of the worksheet you want. Select View Code. Insert this code. This
is just a sample, of course, but should get you started. James

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveWorkbook
If [a1] = 1 Then
.BuiltinDocumentProperties("Category") = "One"
ElseIf [a1] = 2 Then
.BuiltinDocumentProperties("Category") = "Two"
End If
End With
End Sub


Pastel Hughes wrote:
I would like some of my files to automatically generate a value in the File
Property field "Category", based on the contents of a specified cell (eg.
A1). If it is possible to do this, how would one go about it?

Pastel, I'm glad that worked for you. James
Pastel Hughes wrote:
James,

Bingo! This works perfectly.
Now I owe you one.
But how to pay?

Thanks again!

"Zone" wrote:

Pastel,
You're welcome. Still here. I'm not the largest brain in here by
any means, but I try to help people who have issues I have run into,
especially if the large brains don't answer right away. I believe your
assessment of the situation, namely that since the change to the sheet
came from an external source, A1's formula hasn't changed, so Excel
considers that no change has occurred, event-wise. Nevertheless, since
A1's VALUE has changed, in reality a change has occurred to the sheet.
We should be able to detect this change. If you have not changed
Excel's calculation mode from the default setting (automatic), a change
in A1's value should trigger a recalculation of the sheet. Therefore,
change the first line of your code to look like this:

Private Sub Worksheet_Calculate()

and we'll try to trigger on the change to A1's value. Let me know how
this works! James

Pastel Hughes wrote:
James,

The formula contains links to two other sheets.
All of the direct changes take place in the linked sheets.
If the event fires only for direct changes to the active sheet,
this could explain why the property does not get updated.
I guess this would require some additional script in the VB module.
The trouble is, I don't know what that would be.
Thanks for staying with me.


"Zone" wrote:

Pastel,
No problem. This event fires whenever any change is made to the
worksheet. I presumed you would be making changes to the worksheet, so
that's what I set the event procedure to capture. What is the formula
in A1? We'll try agin.
James

Pastel Hughes wrote:
James,

You'll have to forgive me, as I am a novice to VB for Excel.
This works ... sort of. It produces the text in the Category field,
however, there is a formula in A1 that changes according to a number of
variables.
The category text does not change when the result of this formula changes,
unless I copy the formula over itself. Do you have any idea how I might get
around this?

Thanks again

"Zone" wrote:

Pastel, you'll need an event procedure. Right-click on the sheet tab
of the worksheet you want. Select View Code. Insert this code. This
is just a sample, of course, but should get you started. James

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveWorkbook
If [a1] = 1 Then
.BuiltinDocumentProperties("Category") = "One"
ElseIf [a1] = 2 Then
.BuiltinDocumentProperties("Category") = "Two"
End If
End With
End Sub


Pastel Hughes wrote:
I would like some of my files to automatically generate a value in the File
Property field "Category", based on the contents of a specified cell (eg.
A1). If it is possible to do this, how would one go about it?