search

ggreen86 / XLSX-Workbook-Class

VFP Class to Create an XLSX Workbook without Excel Automation or Installed
45 stars 16 forks source link

Create and populate drop-down list inside a cell #104

Closed nmossvfp closed 5 months ago

nmossvfp commented 8 months ago

Hi Greg,

Can you please tell me how to create a drop-down list inside of a specific cell? Let's say it would be just "Yes" or "No" and not editable just selectable with the mouse like any list drop-down.

In addition to a specific cell by specifying the page (row, column) how could I create one for an entire column where the same drop-down choices would show in every row except for the header line?

ggreen86 commented 8 months ago

See the Demo() method in the class. On line 865 has an example of creating a simple cell validation (drop-down):

this.SetCellValidation(lnWb, lnSh1, 13, 1, LIST_VALID_TYPE, , , , , , , , , "Yes,No,Maybe")

Use the method above to set a range of cells via a FOR-LOOP, changing the row number to the index of the FOR-LOOP (there is not a Set Range method). See the documentation for an explanation of the method parameters. A named range example is also provided in lines 880 to 884 (the list of values are added to a hidden sheet which is then referenced as a Named Range in the drop-down).

Greg

From: nmossvfp @.> Sent: Friday, December 22, 2023 1:43 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: [ggreen86/XLSX-Workbook-Class] Create and populate drop-down list inside a cell (Issue #104)

Hi Greg,

Can you please tell me how to create a drop-down list inside of a specific cell? Let's say it would be just "Yes" or "No" and not editable just selectable with the mouse like any list drop-down.

In addition to a specific cell by specifying the page (row, column) how could I create one for an entire column where the same drop-down choices would show in every row except for the header line?

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/104, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33P3N2E4A73MMBL742TYKXID5AVCNFSM6AAAAABBAEYJ3CVHI2DSMVQWIX3LMV43ASLTON2WKOZSGA2TIMZYGU3TAMQ. You are receiving this because you are subscribed to this thread.Message ID: @.**@.>>

ggreen86 commented 8 months ago

In general, see the Demo() method for examples of usage for a lot of the methods. The program excelxlsxtest.prg is also provided which creates a xlsx file to show the results of the Demo() method (see the folder ‘Program Demos’).

From: nmossvfp @.> Sent: Friday, December 22, 2023 1:43 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: [ggreen86/XLSX-Workbook-Class] Create and populate drop-down list inside a cell (Issue #104)

Hi Greg,

Can you please tell me how to create a drop-down list inside of a specific cell? Let's say it would be just "Yes" or "No" and not editable just selectable with the mouse like any list drop-down.

In addition to a specific cell by specifying the page (row, column) how could I create one for an entire column where the same drop-down choices would show in every row except for the header line?

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/104, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33P3N2E4A73MMBL742TYKXID5AVCNFSM6AAAAABBAEYJ3CVHI2DSMVQWIX3LMV43ASLTON2WKOZSGA2TIMZYGU3TAMQ. You are receiving this because you are subscribed to this thread.Message ID: @.**@.>>

nmossvfp commented 8 months ago

Thanks! I was able to use setcellvalidation() to create the columns I need and the drop choices do appear however...

1) I don't see in the examples a way to specify a conditional formatting request based on the user's selection made. For example, to change the cell to Red background when the focus is lost if 'No' was selected from the drop-down or Green background if 'Yes' was selected. Similar to how an IIF in a VFP grid Dynamic backcolor works.

2) I have a .SETCELLFONT command which already changes the column values to Arial 12 for my Yes/No column. This causes the Yes, No options in the drop-down when selected to appear in a much smaller font than the row contents it looks like Arial 9 or smaller. I don't see where to apply a Font and/or Font size parameter in .Setcellvalidation() Is there one?

I will continue to look through the DEMO method code. It's possible the answers are there i am just unfamiliar with it.

ggreen86 commented 8 months ago

The method to create the drop-down does not have the capability to also set dynamic formatting. There is example code in the demo method for setting based on the dynamic cell values.

From: nmossvfp @.> Sent: Friday, December 22, 2023 2:44 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Create and populate drop-down list inside a cell (Issue #104)

Thanks! I was able to use setcellvalidation() to create the columns I need and the drop choices do appear however...

  1. I don't see in the examples a way to specify a conditional formatting request based on the user's selection made. For example, to change the cell to Red background when the focus is lost if 'No' was selected from the drop-down or Green background if 'Yes' was selected. Similar to how an IIF in a VFP grid Dynamic backcolor works.
  2. I have a .SETCELLFONT command which already changes the column values to Arial 12 for my Yes/No column. This causes the Yes, No options in the drop-down when selected to appear in a much smaller font than the row contents it looks like Arial 9 or smaller. I don't see where to apply a Font and/or Font size parameter in .Setcellvalidation() Is there one?

I will continue to look through the DEMO method code. It's possible the answers are there i am just unfamiliar with it.

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/104#issuecomment-1868021009, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33KRV3DC6HVXDM2QKLDYKXPHFAVCNFSM6AAAAABBAEYJ3CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNRYGAZDCMBQHE. You are receiving this because you commented.Message ID: @.**@.>>

ggreen86 commented 8 months ago

I would be interested in seeing how you set the dynamic cell formatting based on the cell value. Please provide me a simple xlsx file that will show how you do this. Possibly I can add the feature…

From: nmossvfp @.> Sent: Friday, December 22, 2023 2:44 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Create and populate drop-down list inside a cell (Issue #104)

Thanks! I was able to use setcellvalidation() to create the columns I need and the drop choices do appear however...

  1. I don't see in the examples a way to specify a conditional formatting request based on the user's selection made. For example, to change the cell to Red background when the focus is lost if 'No' was selected from the drop-down or Green background if 'Yes' was selected. Similar to how an IIF in a VFP grid Dynamic backcolor works.
  2. I have a .SETCELLFONT command which already changes the column values to Arial 12 for my Yes/No column. This causes the Yes, No options in the drop-down when selected to appear in a much smaller font than the row contents it looks like Arial 9 or smaller. I don't see where to apply a Font and/or Font size parameter in .Setcellvalidation() Is there one?

I will continue to look through the DEMO method code. It's possible the answers are there i am just unfamiliar with it.

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/104#issuecomment-1868021009, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33KRV3DC6HVXDM2QKLDYKXPHFAVCNFSM6AAAAABBAEYJ3CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNRYGAZDCMBQHE. You are receiving this because you commented.Message ID: @.**@.>>

nmossvfp commented 8 months ago

There is example code in the demo method for setting based on the dynamic cell values. >>>

I am looking through the Demo() code thoroughly and unless I am just missing it ? can you please tell me how I would add conditional formatting to any cell even one at a time?

I know that I can change the Font and Background color of a cell with the following: this.SetCellFont(lnWb, lnSh2, 1, 1, "Arial", 14, True, False, RGB(255,255,255)) this.SetCellFill(lnWb, lnSh2, 1, 1, RGB(51,102,255))

But in Excel I can specify a user defined conditional background color based on data entry. So for example if someone enters "Yes" and Tabs out the back color becomes Green and if they enter "No" the back color becomes Red etc. In VFP this would be backcolor=IIF(myentry="Yes", RGB(0,255,0), RGB(255,0,0)) simplified for 'No' as an otherwise.

Can I do that ?

ggreen86 commented 8 months ago

Please run the program “excelxlsxtest.prg” which will create the file “ExcelTest.xlsx” (located in the same directory). Open this file and look at the tabs; the 7th tab shows various examples of conditional formatting created by the methods for conditional formatting. This xlsx file is created by the Demo() method. Look in that method for adding sheet 7 and then you have the examples.

Also, you can send me a sample spreadsheet with the cell with a drop-down ‘Yes/No’ and the associated formatting and I will look at to see if it is supported.

Greg

From: nmossvfp @.> Sent: Tuesday, December 26, 2023 2:30 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Create and populate drop-down list inside a cell (Issue #104)

There is example code in the demo method for setting based on the dynamic cell values. >>>

I am looking through the Demo() code thoroughly and unless I am just missing it ? can you please tell me how I would add conditional formatting to any cell even one at a time?

I know that I can change the Font and Background color of a cell with the following: this.SetCellFont(lnWb, lnSh2, 1, 1, "Arial", 14, True, False, RGB(255,255,255)) this.SetCellFill(lnWb, lnSh2, 1, 1, RGB(51,102,255))

But in Excel I can specify a user defined conditional background color based on data entry. So for example if someone enters "Yes" and Tabs out the back color becomes Green and if they enter "No" the back color becomes Red etc. In VFP this would be backcolor=IIF(myentry="Yes", RGB(0,255,0), RGB(255,0,0)) simplified for 'No' as an otherwise.

Can I do that ?

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/104#issuecomment-1869733696, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33MZ6DQVR34QJJ4FYKDYLMQU7AVCNFSM6AAAAABBAEYJ3CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNRZG4ZTGNRZGY. You are receiving this because you commented.Message ID: @.**@.>>

nmossvfp commented 8 months ago

Just to clarify with steps the feature that I am looking for it is called Conditional Formatting. This adds a formatting condition such as a colored background to an otherwise blank cell that an Excel user will populate at a later time.

  1. Click on a cell for which you want to apply conditional formatting.

  2. On the main toolbar click “Conditional Formatting” and from the drop-down select “Highlight Cells Rules” and from the next drop-down select “Equal To”.

  3. On the “Equal To” dialog, enter a text condition such as “Yes” and then select an associated color from the “with” list such as “Green Fill…” etc. Click the OK button.

  4. The conditional formatting is applied and when the condition is met for that cell, example a user types “Yes” then the background color turns Green.

Is this supported ?

Thank You.

ggreen86 commented 8 months ago

Yes – read the user documentation. See the section “Methods – Conditional Formatting” (page 71)

From: nmossvfp @.> Sent: Wednesday, January 10, 2024 10:56 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Create and populate drop-down list inside a cell (Issue #104)

Just to clarify with steps the feature that I am looking for it is called Conditional Formatting. This adds a formatting condition such as a colored background to an otherwise blank cell that an Excel user will populate at a later time.

  1. Click on a cell for which you want to apply conditional formatting.
  2. On the main toolbar click “Conditional Formatting” and from the drop-down select “Highlight Cells Rules” and from the next drop-down select “Equal To”.
  3. On the “Equal To” dialog, enter a text condition such as “Yes” and then select an associated color from the “with” list such as “Green Fill…” etc. Click the OK button.
  4. The conditional formatting is applied and when the condition is met for that cell, example a user types “Yes” then the background color turns Green.

Is this supported ?

Thank You.

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/104#issuecomment-1885128005, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33PDKKFKZO27XKZOO2TYN22Y7AVCNFSM6AAAAABBAEYJ3CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQOBVGEZDQMBQGU. You are receiving this because you commented.Message ID: @.**@.>>

nmossvfp commented 8 months ago

the section “Methods – Conditional Formatting” (page 71)<<<

I found this in the Release 39 documentation but I am a few releases behind. Was it introduced in Release 39?

ggreen86 commented 8 months ago

Was introduced in the last release.

From: nmossvfp @.> Sent: Wednesday, January 10, 2024 1:48 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Create and populate drop-down list inside a cell (Issue #104)

the section “Methods – Conditional Formatting” (page 71)<<<

I found this in the Release 39 documentation but I am a few releases behind. Was it introduced in Release 39?

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/104#issuecomment-1885427927, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33MQKRVDWCIBP6FVLODYN3O7PAVCNFSM6AAAAABBAEYJ3CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQOBVGQZDOOJSG4. You are receiving this because you commented.Message ID: @.**@.>>

ggreen86 commented 5 months ago

Closing issue. Conditional formatting provided in R40 onward.