0

Picture below, you will see the issue immediately.

I wanted to ask, if somebody can tell me where my mistake is. I try to generate Option Buttons aligned to cells. To do that I set ranges and tell excel to create an OptionButton for every cell in a column (running variable down the rows, starting at row 7):

sub Test()
Dim runvar As Integer
runvar = 7

 While runvar < 31

     With ActiveSheet.OptionButtons.Add(Range("$A$" & runvar).Left, Range("$A$" & runvar).Top, Range("$A$" & runvar).Width, Range("$A$" & runvar).Height)
       .Name = "A" & runvar
       .Value = xlOff
       .LinkedCell = "$A$7"
       .Display3DShading = False
       .Select
    End With

   ActiveSheet.Shapes.Range(Array("A" & runvar)).Select
    Selection.Characters.Text = ""
    
    runvar = runvar + 1
 Wend
End Sub

Problem: If I do so, the OptionButtons are shifted every 2-3 cells by 0.75. Please see how it looks like:

Misaligned Buttons

Checked it and every 2 or 3 cells the buttons are misaligned by 0.75. I do not know if this is a systematically mistake and I can just move the button every now and then 0.75 up or if this would screw up the table at other pc. Any idea?

Thank you very much in advance!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
DennisHK
  • 1
  • 1
    If I copy your code to a brand new workbook and run it, the option boxes are perfectly aligned in the rows. I cannot reproduce your issue. – Pᴇʜ Mar 16 '22 at 13:46
  • 1
    Thank you very much for editing my post and checking! I tried and copied all scripts and even the whole structure into a clean Excel sheet. All the functions work as before, but this one works now flawlessly, too. Should have done it before, but it is the first time I encounter such a strange mistake. Sorry for asking! If somebody looks this up: try to copy paste everything in a fresh book - works like a charme! Thanks again! – DennisHK Mar 16 '22 at 14:13
  • Maybe some strange format issues. I saw a lot of strange issues in Excel that disappeared by copying everything into a new workbook. But I also endet up only using the binary `xlsb` format instead of the open XLM formats `xlsm`/`xlsx`. The binary format turned out to be much more stable (and has even smaller files). – Pᴇʜ Mar 16 '22 at 14:19
  • Sorry for double post: This function started misfunctioning again - Then I found the reason for me: Using double monitor with both same resolution, but one of it is 125% zoomed, the other one 100% zoomed. Sometimes, when everything stayed on the first monitor, it was fine. As soon as I changed the Excel window to the other it started bugging. Changed both to 100% and it works several times now! Edit: Thanks for the advice with the binary! Will definitely keep that in mind! – DennisHK Mar 16 '22 at 14:28
  • Well, I have both monitors (4k) on 150 % so it looks to be the issue when both are different zoom values. – Pᴇʜ Mar 16 '22 at 14:31

0 Answers0