If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Color in Form Fields
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! -- JWeaver |
#2
|
|||
|
|||
Color in Form Fields
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 On Fri, 29 Aug 2008 13:28:00 -0700, JWeaver wrote: 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! |
#3
|
|||
|
|||
Color in Form Fields
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!! -- JWeaver "Gord Dibben" wrote: 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 On Fri, 29 Aug 2008 13:28:00 -0700, JWeaver wrote: 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! |
#4
|
|||
|
|||
Color in Form Fields
Remove this line
OldCell.Borders.LineStyle = xlLineStyleNone Gord On Tue, 2 Sep 2008 06:59:15 -0700, JWeaver wrote: 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!! |
#5
|
|||
|
|||
Color in Form Fields
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! -- JWeaver "Gord Dibben" wrote: Remove this line OldCell.Borders.LineStyle = xlLineStyleNone Gord On Tue, 2 Sep 2008 06:59:15 -0700, JWeaver wrote: 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!! |
#6
|
|||
|
|||
Color in Form Fields
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 On Tue, 2 Sep 2008 10:31:02 -0700, JWeaver wrote: 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! |
Thread Tools | |
Display Modes | |
|
|