Open UserForm from specific cell. If empty -- grey instruction text appears. If NOT empty, cell value appears

[DericVonBraatson]

I'm opening my UserForm1 by selecting a specific cell (any cell within a range of A2:A100). I say "specific cell" because UserForm1 is pulling info back into my TextBox1 and TextBox2.
However, if any cell within my range of A2:A100 is empty, I want UserForm1 to open with a grey instruction text.

My code works, but ONLY from inside of Visual Basics by hitting the run/play button. My VBA code appears to cancel itself out if I'm selecting from the Excel spreadsheet, range A2:A100 in order to open my UserForm1.

I believe my problem is that I don't have some kind of "IF EMPTY" Statement in my code that goes into SHEET1. I'm not an experienced enough user to know how to create that.

I would appreciate any code help on this madness. Thank you!!!

SHEET1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


' This retrieves info back into UserForm if values are present within a specific cell


    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
        
    UserForm1.TextBox1 = Sheets("Sheet1").Cells(Target.Row, "A")
    UserForm1.TextBox2 = Sheets("Sheet1").Cells(Target.Row, "B")


    UserForm1.Show
    
    End If


End Sub

UserForm1

Private Sub UserForm_Initialize()


' This sets up the grey instruction text for two TextBoxes


    TextBox1.ForeColor = &HC0C0C0
    TextBox1.Text = "Input 1st Text Here"


    TextBox2.ForeColor = &HC0C0C0
    TextBox2.Text = "Input 2nd Text Here"


    Me.CommandButton1.SetFocus


End Sub
Private Sub TextBox1_Enter()


' If new text is entered, the Textbox values turns black and will send to Excel Spreadsheet


    With TextBox1
    If .Text = "Input 1st Text Here" Then
    .ForeColor = &H80000008 '<~~ Black Color
    .Text = ""
    End If
    End With


End Sub
Private Sub TextBox1_AfterUpdate()


' If new text is not inputted (or even erased), the Textbox returns to grey instructions and will NOT send to Excel Spreadsheet


    With TextBox1
    If .Text = "" Then
    .ForeColor = &HC0C0C0
    .Text = "Input 1st Text Here"
    End If
    End With


End Sub
Private Sub TextBox2_Enter()


' If new text is entered, the Textbox values turns black and will send to Excel Spreadsheet


    With TextBox2
    If .Text = "Input 2nd Text Here" Then
    .ForeColor = &H80000008
    .Text = ""
    End If
    End With


End Sub


Private Sub TextBox2_AfterUpdate()


' If new text is not inputted (or even erased), the Textbox returns to grey instructions and will NOT send to Excel Spreadsheet


    With TextBox2
    If .Text = "" Then
    .ForeColor = &HC0C0C0
    .Text = "Input 2nd Text Here"
    End If
    End With


End Sub
Private Sub CommandButton1_Click()


'CommandButton1 Sends Info to Excel Spreadsheet


    ActiveCell.Offset(0, 0).Value = Me.TextBox1.Value
    ActiveCell.Offset(0, 1).Value = Me.TextBox2.Value


    Unload Me


End Sub

Here's my Excel file example:
https://1drv.ms/x/s!AvBxwrOYtc3OgUC9qgGx-FV6Zx_F

Photo of my Excel example:
https://1drv.ms/i/s!AvBxwrOYtc3OgUGjosZFLY2KAfPS

 

[AlphaFrog]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_SelectionChange([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)


[color=green]' This retrieves info back into UserForm if values are present within a specific cell[/color]


    [color=darkblue]If[/color] Target.Count > 1 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Target, Range("A2:A100")) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] UserForm1.Show


[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()


[color=green]' This sets up the grey instruction text for two TextBoxes[/color]


    [color=darkblue]If[/color] ActiveCell.Value <> "" [color=darkblue]Then[/color]
        Me.TextBox1 = ActiveCell.Value
    [color=darkblue]Else[/color]
        TextBox1.ForeColor = &HC0C0C0
        TextBox1.Text = "Input 1st Text Here"
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]If[/color] ActiveCell.Offset(, 1).Value <> "" [color=darkblue]Then[/color]
        TextBox2 = ActiveCell.Offset(, 1).Value
    [color=darkblue]Else[/color]
        TextBox2.ForeColor = &HC0C0C0
        TextBox2.Text = "Input 2nd Text Here"
    [color=darkblue]End[/color] [color=darkblue]If[/color]


    Me.CommandButton1.SetFocus


[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[DericVonBraatson]

Wow! Thank you for taking the time out to help, Alpha Frog.

It works but it has also made two significant changes, so my question is:

1.) What can I do to make the grey instruction text automatically disappear when someone enters to type over it? But at the same time, if a user types into the TexBox but decides to erase the entry, the grey instruction text returns.

2.) What can I do to prevent the grey instruction text from transferring to the Excel spreadsheet, in other words, only transfer text that has been entered by the user?

Thanks again for your help!

 

[DericVonBraatson]

Never mind, I got it by adding back in some of the earlier code mixed with yours. Thanks for the help again!

Private Sub TextBox1_Enter()


' If new text is entered, the Textbox values turns black and will send to Excel Spreadsheet


    With TextBox1
    If .Text = "Input 1st Text Here" Then
    .ForeColor = &H80000008 '<~~ Black Color
    .Text = ""
    End If
    End With


End Sub
Private Sub TextBox1_AfterUpdate()


' If new text is not inputted (or even erased), the Textbox returns to grey instructions and will NOT send to Excel Spreadsheet


    With TextBox1
    If .Text = "" Then
    .ForeColor = &HC0C0C0
    .Text = "Input 1st Text Here"
    End If
    End With


End Sub

 

[DericVonBraatson]

Unfortunately, this still leaves me with issue # 2:

2.) What can I do to prevent the grey instruction text from transferring to the Excel spreadsheet, in other words, only transfer text that has been entered by the user?

 

[AlphaFrog]

Unfortunately, this still leaves me with issue # 2:

2.) What can I do to prevent the grey instruction text from transferring to the Excel spreadsheet, in other words, only transfer text that has been entered by the user?

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()


[color=green]'CommandButton1 Sends Info to Excel Spreadsheet[/color]


    [B][color=darkblue]If[/color] TextBox1.Text <> "Input 1st Text Here" [color=darkblue]Then[/color][/B] ActiveCell.Offset(0, 0).Value = Me.TextBox1.Value
    [B][color=darkblue]If[/color] TextBox2.Text <> "Input 2nd Text Here" [color=darkblue]Then[/color][/B] ActiveCell.Offset(0, 1).Value = Me.TextBox2.Value


    Unload Me


[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[DericVonBraatson]

thank you!