Comment Boxes & Dropdown Arrows only appearing momentarily - Excel 2016

[cjcass]

Hi,
I have a file which used to work fine but now has a problem - the only difference is that I have upgraded from Excel 2010 to 2016 (which I guess must be the issue!?). The comment boxes and dropdown list arrows only appear momentarily (a quick flash on/off) with this particular file in 2016, however, the file continues to work fine in Excel 2010. Strangely though I have other files with dropdowns and comments boxes which work fine in Excel 2016 - so am guessing it's nothing to do with settings in Excel 2016...????
Any thoughts would be much appreciated.

 

[Roderick_E]

userform1.repaint perhaps??? Difficult to tell. If your other userforms are working fine then it is probably something in the code.

 

[cjcass]

Hi, thanks for replying.
I cannot find userform1.repaint in any of my code - if that's that what you mean?

 

[Roderick_E]

Hi, thanks for replying.
I cannot find userform1.repaint in any of my code - if that's that what you mean?

No I'm sorry, I meant, are you using a userform? If so, maybe you need to force a repaint??? The line I posted.

 

[cjcass]

Hi, the dropdowns are standard, ie. inserted from the 'Data Validation' route and the comment boxes are from right click the cell and 'Insert Comment'.
In your 'userform1.Repaint' what does the '1' refer to?

Regarding the comment boxes; I have password protected the worksheet but the following code allows the user to show/hide the comment indicators:

If Application.DisplayCommentIndicator = xlCommentIndicatorOnly Then
    Application.DisplayCommentIndicator = False
  Else
    Application.DisplayCommentIndicator = xlCommentIndicatorOnly
  End If

Regarding the dropdowns, in a similar fashion I have two macros - one that adds a dropdown to a selected cell and one that removes it, but these are on a separate worksheet to where I have a problem:

‘add dropdown
ActiveCell.Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Rationale"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
 
‘remove dropdown
ActiveCell.Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With

Not sure if these codes are adding to the problem??

 

[Roderick_E]

Hi, the dropdowns are standard, ie. inserted from the 'Data Validation' route and the comment boxes are from right click the cell and 'Insert Comment'.
In your 'userform1.Repaint' what does the '1' refer to?

Regarding the comment boxes; I have password protected the worksheet but the following code allows the user to show/hide the comment indicators:

If Application.DisplayCommentIndicator = xlCommentIndicatorOnly Then
    Application.DisplayCommentIndicator = False
  Else
    Application.DisplayCommentIndicator = xlCommentIndicatorOnly
  End If

Regarding the dropdowns, in a similar fashion I have two macros - one that adds a dropdown to a selected cell and one that removes it, but these are on a separate worksheet to where I have a problem:

‘add dropdown
ActiveCell.Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Rationale"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
 
‘remove dropdown
ActiveCell.Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With

Not sure if these codes are adding to the problem??

Thanks for the clarification. Userform1 is the default name of the first userform added to a workbook project. I see now my answer doesn't apply to your situation.
Nothing stands out as the obvious problem in your code. I would try unprotecting the sheet/workbook then run the code and see if the same problem occurs. If not, the issue is probably something in how the cells where you are putting the dropdowns and comments are being protected.
Secondly, make sure your named range =Rationale actually exists.
Lastly, you are using ActiveCell, perhaps you mean to? But if the code attempts to run on a protected "active" cell it would fail.
Hope this points you in the right direction.

 

[cjcass]

Hi, thanks for coming back, in response to your comments:
1. The issue remains whether the sheets are protected/unprotected
2. =Rationale does exist
3. Yes, ActiveCell is by design and on this sheet the cells are not protected.

One thing which may be a factor or not is my sheet with comments box issue is protected as follows:
Sheet16.Protect Password:="tcb", UserInterFaceOnly:=True

 

[Roderick_E]

Hmm, I'm stumped

 

[cjcass]

ok, thanks for your time on it.
still no solution found on this one - if anyone has got any bright ideas please let me know!!

 

[cjcass]

Ok, so I've got some more info that may lead us closer to solving the problem - have been scouring forums for a steer and found that someone else had a similar problem and they found that when they 'freeze panes' on the worksheet in question the problem goes - I have just tried this and indeed it works!!
However, we all know that this isn't really solving the problem and is a bit of a clunky workaround - so how could this be connected to the issue? this must bring us closer to identifying the issue... any thoughts much appreciated??