Color in Form Fields

[JWeaver]

I have a Form that I created in Excel that has been protected so that Users
can tab to the next box that needs completed. Is there a way to highlight
the field once you tab to it so that the User will know where they are on the
Form? I do not want the color to print on the Form though.

Thanks!

 

[Gord Dibben]

How is your Tabbing set up?

Unlocked cells on a protected sheet?

This sheet event code will work on a protected sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
ActiveSheet.Unprotect Password:="justme"
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
OldCell.Borders.LineStyle = xlLineStyleNone
End If
Set OldCell = Target
OldCell.Interior.ColorIndex = 6
OldCell.Borders.LineStyle = xlContinuous
Else
If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = Union(OldCell, Target)
End If
End If
ActiveSheet.Protect Password:="justme"
End Sub

Will color the activecell yellow.

Note: will wipe out existing background color of activecell cell unless BG
color is due to CF

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module. Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

 

[JWeaver]

Thanks, Gord!

That worked to color the cell, however, it wiped out my cell border I had in
place. Can I modify the code you specified to put back the cell border? I
can't set up to print gridlines because it puts lines where I don't want them
on the Form.

Thanks!!

 

[Gord Dibben]

Remove this line

OldCell.Borders.LineStyle = xlLineStyleNone


Gord

 

[JWeaver]

I removed that line and now the colors aren't working. Does something else
need to be changed as well?

Also, I removed the code completely and then repasted it and it still didn't
work. I changed the password in your code to the one I had set for me file
and it still didn't fix it (I had already changed the password to match mine
in the one that worked to change the colors but forgot to mention it in my
earlier post).

Thanks!

 

[Gord Dibben]

I tested with this code..................

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
ActiveSheet.Unprotect Password:="justme"
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If
Set OldCell = Target
OldCell.Interior.ColorIndex = 6
OldCell.Borders.LineStyle = xlContinuous
Else
If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = Union(OldCell, Target)
End If
End If
ActiveSheet.Protect Password:="justme"
End Sub

My testing with random cells in columns A, D, and G given a thick border,
protection set to "unlocked" and sheet protected with the password
"justme"(quotes required). You can safely change that password with no
effect on the code.

Hit Tab and next unlocked cell is selected, color to yellow and border
preserved.

Tab to next unlocked cell and color is yellow and border preserved.

Previous cell loses color and border has been preserved.

Do you want the tabbed to cells to reatin the color yellow?

I would not think so given your requirement to highlight current cell for
user.

Somehow you may have disabled events while booping about.

Sub enable_events()
Application.EnableEvents = True
End Sub


Gord