How can I stop a Calc Command Button (Type URL) from changing Background Color when clicking it?

English
,
#1

I wrote a macro that creates a Command Button of Type com.sun.star.form.FormButtonType.URL. It is anchored to and has the size of the currently selected cell. The code sets the background color and the font of the button.

When I click on the button, the background color changes to a darker version of the color it was set to.

Is there a way to prevent this?

Here is the part of the code that creates The button, puts it in a Shape, and adds it to the Sheet:

  ' Create and configure a Command Button with Type=URL
  oButton                 = oDoc.createInstance("com.sun.star.form.component.CommandButton")
  oButton.ButtonType      = com.sun.star.form.FormButtonType.URL
  oButton.Name            = sButtonLabel & "_Button"
  oButton.Label           = sButtonLabel
  oButton.TargetURL       = sURL
  oButton.FocusOnClick    = FALSE
  oButton.Repeat          = FALSE
  obutton.BackgroundColor = RGB(255, 255, 215) ' ***** WHY DOES THIS CHANGE (DARKEN) AFTER I CLICK ON IT??? *****
  obutton.TextColor       = RGB(0, 150, 200)
' oButton.FontName        = "Arial"            ' Use FontDescriptor instead

  ' Create and configure a FontDescriptor for the Button and apply it
  oFontDesc              = oButton.FontDescriptor
' oFontDesc              = oDoc.createInstance("com.sun.star.awt.FontDescriptor") ''''' MABXXX WHY DOESN'T THIS WORK???
  oFontDesc.Name         = "Arial"
  oFontDesc.Height       = 12
  oFontDesc.Weight       = 175     ' This is a PERCENTAGE - Default 100% is NORMAL (400), 175% is BOLD (700)
  oButton.FontDescriptor = oFontDesc

  ' Create and configure a Shape to put the Button in
  oShape                = oDoc.createInstance("com.sun.star.drawing.ControlShape")
  oShape.Name           = sButtonLabel & "_Shape"
  oShape.Control        = oButton        ' Set the Button as the Control in the Shape
  oSheet.DrawPage.add(oShape)            ' Add the Shape (that contains the Button) to the Sheet - MUST be ABOVE the following lines
  oShape.Size           = oCell.Size     ' Size the Shape with the Size of the selected Cell
  oShape.Position       = oCell.Position ' Position the Shape into the selected Cell
  oShape.Anchor         = oCell          ' Anchor the Shape to the selected Cell
  oShape.ResizeWithCell = TRUE           ' Resize the Shape every time the Cell size changes
  oShape.SizeProtect    = FALSE          ' Allow the Shape to RESIZE, TRUE prevents ResizeWithCell
  oShape.MoveProtect    = TRUE           ' Don't allow the Shape to MOVE
#2

Please, could you upload a test file so that we can look at the effect you mentioned together?

#3

(sorry. for some reason, empty lines are being removed from my reply)

Thank you for looking at my request. Here you go!

This is the Calc file I use for testing:
Test1.ods (10.2 KB)

Here is the full Macro:
create_button_link.doc (7.4 KB)
It’s really just a .txt file with Windows line ends, but I had to rename it to .doc to get it to upload.
The Macro creates a Hyperlink Button in the selected Cell.

Be gentle. This is my first ever Calc Macro and my first use of Basic since 1980. Feel free to comment if you see better ways of doing things.

I run this Macro with a Shortcut Key. To use it, I select a Cell and click the key.

  • Only ONE Cell must be selected
  • The selected Cell must contain either a =HYPERLINK() Formula or an actual Hyperlink
  • The Macro first asks you for a label for the Button it creates
  • If the Cell contains a Hyperlink and the Hyperlink has a Text value, the label defaults to that
  • The Macro then creates a Button anchored to the Cell, with the same size and position as the cell
  • The Button has a light yellow background with bold, medium-blue text with Arial 12 font
  • It re-sizes when the Cell size changes

HERE IS THE PROBLEM I AM ASKING ABOUT: When I click on one of these buttons, the background color changes to a darker yellow.

ANOTHER PROBLEM: if the Cell is a MERGED Cell, the size fits only the FIRST Cell in the merged range. I’m trying to find a fix for that.

The test Calc file has a number of cells containing this HYPERLINK Formula:
=HYPERLINK("#$Sheet1.A1:A1")
and one Hyperlink with URL “https://google.com/” and Text “Google”

I know that I could just create a Hyperlink as a Button, but that ability was just an afterthought. The original use case was to create Buttons from =HYPERLINK() Formulas so I could use them to jump to different locations in my spreadsheets. I put the Buttons in Frozen Rows to create my own navigation bars.

Any ideas?

Thanks again,
—Mark

1 Like
#4

I forgot to say…

When I scroll the spreadsheet, the background color returns to how it was set.

#5

AFACIT, it started with 7.2.0.1 (on Windows).
Attached is a file created with version 7.1.8.1. The .gif below shows the difference.

Indeed.
â €
7181vs7201
â €
file_7181.ods (12.3 KB)

1 Like
#6

Mark, thanks for the good example and observation!
I think it doesn’t matter that the buttons are created by a macro, as our colleague @cwolan’ s example shows.
I think this is a bug that should be reported.

Great for a start!
In 1980 I wrote programs for IBM/360 in PL/1 and macro assembler. :slight_smile:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.