+ Reply to Thread
Results 1 to 2 of 2

Gray Example Text in Empty Cells Until User Types in Cell

  1. 03-17-2018, 11:24 AM #1
    beqr
    beqr is offline
    Registered User
    Join Date
    03-17-2018
    Location
    Detroit, Michigan
    MS-Off Ver
    365
    Posts
    1

    Gray Example Text in Empty Cells Until User Types in Cell

    Hello, I'm just scratching the surface of VBA and I'm having trouble with a budget spreadsheet I'm working on.

    Basically, I have my sheet protected so the user may only enter text into specific cells. But when empty, I would like those cells to have gray "example" text such as Paycheck, $0.00, etc.

    I have 2 error messages that consistently pop up. Run-time error 1004 and Run-time error 13. When I UNprotect the sheet, Run-time error 1004 goes away. I get the Run-time error 13 whenever I double-click on a cell edited by a macro.

    The formulas in the sheet work great, but my macro has errors and I don't know how to fix them. I'll post my macro here in case anyone can see where my problem is. Thank you in advance.


    Option Explicit

    'This checks for specific strings in cell values, and formats to gray text if found

    Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Cells.Value
    Case "Paycheck"
    Call FormatCell(Target)
    Case "0"
    Call FormatCell(Target)
    Case "Consistent"
    Call FormatCell(Target)
    Case "Income"
    Call FormatCell(Target)
    Case "Remaining"
    Call FormatCell(Target)
    Case ""
    'This line Defaults the 4 boxes at the top of the document to "Paycheck"
    If Range("E5").Value = "" Then: Range("E5").Value = "Paycheck"
    If Range("J5").Value = "" Then: Range("J5").Value = "Paycheck"
    If Range("E8").Value = "" Then: Range("E8").Value = "Paycheck"
    If Range("J8").Value = "" Then: Range("J8").Value = "Paycheck"
    'This line Defaults the Paycheck amount to $0.00
    If Range("H5").Value = "" Then: Range("H5").Value = "0"
    If Range("M5").Value = "" Then: Range("M5").Value = "0"
    If Range("H8").Value = "" Then: Range("H8").Value = "0"
    If Range("M8").Value = "" Then: Range("M8").Value = "0"
    'This line Defults the M14:M43 Columns to 0
    If Range("M14").Value = "" Then: Range("M14").Value = "0"
    If Range("M15").Value = "" Then: Range("M15").Value = "0"
    If Range("M16").Value = "" Then: Range("M16").Value = "0"
    If Range("M17").Value = "" Then: Range("M17").Value = "0"
    If Range("M18").Value = "" Then: Range("M18").Value = "0"
    If Range("M19").Value = "" Then: Range("M19").Value = "0"
    If Range("M20").Value = "" Then: Range("M20").Value = "0"
    If Range("M21").Value = "" Then: Range("M21").Value = "0"
    If Range("M22").Value = "" Then: Range("M22").Value = "0"
    If Range("M23").Value = "" Then: Range("M23").Value = "0"
    If Range("M24").Value = "" Then: Range("M24").Value = "0"
    If Range("M25").Value = "" Then: Range("M25").Value = "0"
    If Range("M26").Value = "" Then: Range("M26").Value = "0"
    If Range("M27").Value = "" Then: Range("M27").Value = "0"
    If Range("M28").Value = "" Then: Range("M28").Value = "0"
    If Range("M29").Value = "" Then: Range("M29").Value = "0"
    If Range("M30").Value = "" Then: Range("M30").Value = "0"
    If Range("M31").Value = "" Then: Range("M31").Value = "0"
    If Range("M32").Value = "" Then: Range("M32").Value = "0"
    If Range("M33").Value = "" Then: Range("M33").Value = "0"
    If Range("M34").Value = "" Then: Range("M34").Value = "0"
    If Range("M35").Value = "" Then: Range("M35").Value = "0"
    If Range("M36").Value = "" Then: Range("M36").Value = "0"
    If Range("M37").Value = "" Then: Range("M37").Value = "0"
    If Range("M38").Value = "" Then: Range("M38").Value = "0"
    If Range("M39").Value = "" Then: Range("M39").Value = "0"
    If Range("M40").Value = "" Then: Range("M40").Value = "0"
    If Range("M41").Value = "" Then: Range("M41").Value = "0"
    If Range("M42").Value = "" Then: Range("M42").Value = "0"
    If Range("M43").Value = "" Then: Range("M43").Value = "0"
    'This line Defults the lower 2 P50:P69 & P78:P97 Columns to 0
    If Range("P50").Value = "" Then: Range("P50").Value = "0"
    If Range("P51").Value = "" Then: Range("P51").Value = "0"
    If Range("P52").Value = "" Then: Range("P52").Value = "0"
    If Range("P53").Value = "" Then: Range("P53").Value = "0"
    If Range("P54").Value = "" Then: Range("P54").Value = "0"
    If Range("P55").Value = "" Then: Range("P55").Value = "0"
    If Range("P56").Value = "" Then: Range("P56").Value = "0"
    If Range("P57").Value = "" Then: Range("P57").Value = "0"
    If Range("P58").Value = "" Then: Range("P58").Value = "0"
    If Range("P59").Value = "" Then: Range("P59").Value = "0"
    If Range("P60").Value = "" Then: Range("P60").Value = "0"
    If Range("P61").Value = "" Then: Range("P61").Value = "0"
    If Range("P62").Value = "" Then: Range("P62").Value = "0"
    If Range("P63").Value = "" Then: Range("P63").Value = "0"
    If Range("P64").Value = "" Then: Range("P64").Value = "0"
    If Range("P65").Value = "" Then: Range("P65").Value = "0"
    If Range("P66").Value = "" Then: Range("P66").Value = "0"
    If Range("P67").Value = "" Then: Range("P67").Value = "0"
    If Range("P68").Value = "" Then: Range("P68").Value = "0"
    If Range("P69").Value = "" Then: Range("P69").Value = "0"
    If Range("P78").Value = "" Then: Range("P78").Value = "0"
    If Range("P79").Value = "" Then: Range("P79").Value = "0"
    If Range("P80").Value = "" Then: Range("P80").Value = "0"
    If Range("P81").Value = "" Then: Range("P81").Value = "0"
    If Range("P82").Value = "" Then: Range("P82").Value = "0"
    If Range("P83").Value = "" Then: Range("P83").Value = "0"
    If Range("P84").Value = "" Then: Range("P84").Value = "0"
    If Range("P85").Value = "" Then: Range("P85").Value = "0"
    If Range("P86").Value = "" Then: Range("P86").Value = "0"
    If Range("P87").Value = "" Then: Range("P87").Value = "0"
    If Range("P88").Value = "" Then: Range("P88").Value = "0"
    If Range("P89").Value = "" Then: Range("P89").Value = "0"
    If Range("P90").Value = "" Then: Range("P90").Value = "0"
    If Range("P91").Value = "" Then: Range("P91").Value = "0"
    If Range("P92").Value = "" Then: Range("P92").Value = "0"
    If Range("P93").Value = "" Then: Range("P93").Value = "0"
    If Range("P94").Value = "" Then: Range("P94").Value = "0"
    If Range("P95").Value = "" Then: Range("P95").Value = "0"
    If Range("P96").Value = "" Then: Range("P96").Value = "0"
    If Range("P97").Value = "" Then: Range("P97").Value = "0"
    'This line defaults the Consistent section to say "Consistent"
    If Range("G14").Value = "" Then: Range("G14").Value = "Consistent"
    If Range("G15").Value = "" Then: Range("G15").Value = "Consistent"
    If Range("G16").Value = "" Then: Range("G16").Value = "Consistent"
    If Range("G17").Value = "" Then: Range("G17").Value = "Consistent"
    If Range("G18").Value = "" Then: Range("G18").Value = "Consistent"
    If Range("G19").Value = "" Then: Range("G19").Value = "Consistent"
    If Range("G20").Value = "" Then: Range("G20").Value = "Consistent"
    If Range("G21").Value = "" Then: Range("G21").Value = "Consistent"
    If Range("G22").Value = "" Then: Range("G22").Value = "Consistent"
    If Range("G23").Value = "" Then: Range("G23").Value = "Consistent"
    If Range("G24").Value = "" Then: Range("G24").Value = "Consistent"
    If Range("G25").Value = "" Then: Range("G25").Value = "Consistent"
    If Range("G26").Value = "" Then: Range("G26").Value = "Consistent"
    If Range("G27").Value = "" Then: Range("G27").Value = "Consistent"
    If Range("G28").Value = "" Then: Range("G28").Value = "Consistent"
    If Range("G29").Value = "" Then: Range("G29").Value = "Consistent"
    If Range("G30").Value = "" Then: Range("G30").Value = "Consistent"
    If Range("G31").Value = "" Then: Range("G31").Value = "Consistent"
    If Range("G32").Value = "" Then: Range("G32").Value = "Consistent"
    If Range("G33").Value = "" Then: Range("G33").Value = "Consistent"
    If Range("G34").Value = "" Then: Range("G34").Value = "Consistent"
    If Range("G35").Value = "" Then: Range("G35").Value = "Consistent"
    If Range("G36").Value = "" Then: Range("G36").Value = "Consistent"
    If Range("G37").Value = "" Then: Range("G37").Value = "Consistent"
    If Range("G38").Value = "" Then: Range("G38").Value = "Consistent"
    If Range("G39").Value = "" Then: Range("G39").Value = "Consistent"
    If Range("G40").Value = "" Then: Range("G40").Value = "Consistent"
    If Range("G41").Value = "" Then: Range("G41").Value = "Consistent"
    If Range("G42").Value = "" Then: Range("G42").Value = "Consistent"
    If Range("G43").Value = "" Then: Range("G43").Value = "Consistent"
    'This line defaults the Percentage of Income section to say "Income"
    If Range("G50").Value = "" Then: Range("G50").Value = "Income"
    If Range("G51").Value = "" Then: Range("G51").Value = "Income"
    If Range("G52").Value = "" Then: Range("G52").Value = "Income"
    If Range("G53").Value = "" Then: Range("G53").Value = "Income"
    If Range("G54").Value = "" Then: Range("G54").Value = "Income"
    If Range("G55").Value = "" Then: Range("G55").Value = "Income"
    If Range("G56").Value = "" Then: Range("G56").Value = "Income"
    If Range("G57").Value = "" Then: Range("G57").Value = "Income"
    If Range("G58").Value = "" Then: Range("G58").Value = "Income"
    If Range("G59").Value = "" Then: Range("G59").Value = "Income"
    If Range("G60").Value = "" Then: Range("G60").Value = "Income"
    If Range("G61").Value = "" Then: Range("G61").Value = "Income"
    If Range("G62").Value = "" Then: Range("G62").Value = "Income"
    If Range("G63").Value = "" Then: Range("G63").Value = "Income"
    If Range("G64").Value = "" Then: Range("G64").Value = "Income"
    If Range("G65").Value = "" Then: Range("G65").Value = "Income"
    If Range("G66").Value = "" Then: Range("G66").Value = "Income"
    If Range("G67").Value = "" Then: Range("G67").Value = "Income"
    If Range("G68").Value = "" Then: Range("G68").Value = "Income"
    If Range("G69").Value = "" Then: Range("G69").Value = "Income"
    'This line defaults the Percentage of Remaining section to say "Remaining"
    If Range("G78").Value = "" Then: Range("G78").Value = "Remaining"
    If Range("G79").Value = "" Then: Range("G79").Value = "Remaining"
    If Range("G80").Value = "" Then: Range("G80").Value = "Remaining"
    If Range("G81").Value = "" Then: Range("G81").Value = "Remaining"
    If Range("G82").Value = "" Then: Range("G82").Value = "Remaining"
    If Range("G83").Value = "" Then: Range("G83").Value = "Remaining"
    If Range("G84").Value = "" Then: Range("G84").Value = "Remaining"
    If Range("G85").Value = "" Then: Range("G85").Value = "Remaining"
    If Range("G86").Value = "" Then: Range("G86").Value = "Remaining"
    If Range("G87").Value = "" Then: Range("G87").Value = "Remaining"
    If Range("G88").Value = "" Then: Range("G88").Value = "Remaining"
    If Range("G89").Value = "" Then: Range("G89").Value = "Remaining"
    If Range("G90").Value = "" Then: Range("G90").Value = "Remaining"
    If Range("G91").Value = "" Then: Range("G91").Value = "Remaining"
    If Range("G92").Value = "" Then: Range("G92").Value = "Remaining"
    If Range("G93").Value = "" Then: Range("G93").Value = "Remaining"
    If Range("G94").Value = "" Then: Range("G94").Value = "Remaining"
    If Range("G95").Value = "" Then: Range("G95").Value = "Remaining"
    If Range("G96").Value = "" Then: Range("G96").Value = "Remaining"
    If Range("G97").Value = "" Then: Range("G97").Value = "Remaining"
    Case Else
    Target.Cells.Font.Color = &H0
    End Select
    End Sub

    'This clears a cell's contents when someone double clicks the cells to edit its value
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Target.Cells.Value = ""
    End Sub

    'This formats the font color back to black so user data is easily readable
    Private Sub FormatCell(ByVal Target As Range)
    Target.Cells.Font.Color = &H808080
    End Sub

    Sub macroProtect3()
    Sheet2.Protect Password:="0000", UserInterFaceOnly:=True
    'enter code
    Sheet2.Cells(1, 1) = UCase("")

    End Sub
    Reply With QuoteRegister To Reply
  2. 03-17-2018, 11:49 AM #2
    Winon
    Winon is offline
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Gray Example Text in Empty Cells Until User Types in Cell

    Hello beqr,

    Welcome to the Forum!

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    It is also advised that you attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Change event to Convert (calculate) user input if user types % sign
    By soundneedle in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2016, 12:01 PM
  2. User types into user form and information is pulled from the sheet to the userform
    By sdl2 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-04-2015, 12:09 PM
  3. Need an event when user types in a cell?
    By RustyNail in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2015, 03:41 PM
  4. Gray out and lock the text box or dropbox after user has made a entry in it
    By static123 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-29-2014, 12:08 AM
  5. Check for each cell in range if is it empty when user click to some cells
    By cronet in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-01-2013, 04:35 PM
  6. [SOLVED] Gray out cells until user makes input
    By jbeeball in forum Excel General
    Replies: 3
    Last Post: 05-21-2012, 12:48 PM
  7. Text shifts left in Excel Combo Box when user types in entry
    By shearston1010 in forum Excel General
    Replies: 0
    Last Post: 10-20-2005, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1