VBA Code, runs when debugging, not when running. Just changing the look of cells and shapes.

[misseill]

Hi, thank you for this forum, it’s an invaluable resource to me for work. I have searched through your forums and Googled, and can’t find the answer to my issue. Please help!

I have code that will run if I step through it, but not if it is running from Excel. My code changes a shape color, edits and locks/unlocks a range (two cells) on four sheets, and on one sheet, it changes the width of a rectangular picture. This code is all based on a Boolean stored in the same spreadsheet. I have seen similar issues, but in the cases I have found, the issue based on data being opened from an outside source, and the program not having enough time to read the data.

To illustrate, this is the correct layout after stepping through the code:

This is incorrect:

This is my code:

Private Sub showHideNCCOptions(bShow As Boolean)

Dim i As Integer
Dim strSheet As String
Dim strRange As String
Dim shShape As Shape
Dim strSelectRange As String

For i = 1 To 4

Select Case i
Case 1

strSheet = shInputData
strRange = "NCCTab"
strSelectRange = ""
​

Case 2

strSheet = shInputDataBig
strRange = "NCCTabBig"
strSelectRange = ""
​

Case 3

strSheet = shGraphOptions
strRange = "NCCTabGraph"
strSelectRange = "Graph1NumYrs"
​

Case 4

strSheet = shSavedSlides
strRange = "NCCTabSaved"
strSelectRange = "NewNameSlide"
​

End Select
With Worksheets(strSheet)

.Unprotect
​

'unlock the cells first, to change them, then lock/unlock based on bShow
.Range(strRange).Locked = False

If bShow Then

.Select
'if show, make the cell blue and add text.
.Range(strRange).Select
ActiveCell.FormulaR1C1 = "NCC Tab"
Selection.Interior.Color = 6635776 'rgb(0,65,101)

'select the shape that is in the NCC Tab's Range
For Each shShape In .Shapes

If Not Application.Intersect(shShape.TopLeftCell, .Range(strRange)) Is Nothing Then

shShape.Select
With Selection.ShapeRange.Fill

.Visible = msoTrue
If i = 4 Then

.ForeColor.RGB = RGB(255, 255, 255) 'white for saved slides
​

Else

.ForeColor.RGB = RGB(100, 176, 222)
​

End If
.Transparency = 0
.Solid
​

End With
Exit For
​

End If
​

Next shShape

If i = 2 Then

ActiveSheet.Shapes.Range(Array("Picture 61")).Select
Selection.Width = 770.20447
​

End If
​

Else

'if don't show, make the cell not filled, no text
.Select

.Range(strRange).Select
Selection.ClearContents
Selection.Interior.Pattern = xlNone

'select the shape that is in the NCC Tab's Range
For Each shShape In .Shapes

If Not Application.Intersect(shShape.TopLeftCell, .Range(strRange)) Is Nothing Then

shShape.Select
Selection.ShapeRange.Fill.Visible = msoFalse
Exit For
​

End If
​

Next shShape

If i = 2 Then 'make the rectangle wider in inputdatabig

ActiveSheet.Shapes.Range(Array("Picture 61")).Select
Selection.Width = 830.27386
'Selection.ShapeRange.ScaleWidth 1.0779915203, msoFalse, msoScaleFromBottomRight
​

End If
​

End If

'lock based on bShow now
.Range(strRange).Locked = Not bShow 'bshow = true means locked = true so we need a not
If i < 3 Then
.Cells(FindFirstUnlockedCell(True), idbColStart).Select
Else
.Range(strSelectRange).Select
End If
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
​

End With
​

Next i
End Sub

 

[misseill]

(sorry I didn't use the "code" tags!)