Enable and disable

**rajeev.raj** · 11-09-2010, 03:52 PM

Dear Moderators,
I have a question that I made a user formwith textbox1 and textbo2, whatever i am entering in textbox1, the same thing is available in my activesheet. i want that when i will enter in textbox1 and before entering in textbox2, macro check textbox1.value in activesheet column-a, if the same data listed in column a, it allow to enter in textbox2 otherwise some errmsg should apper.
Hoping fot early reply and many many thanks in advance

**jaslake** · 11-09-2010, 04:18 PM

Hi rajeev.raj

I'm not a Moderator but perhaps this will help

Public Sub FindTextBoxValue()
    Dim strFind As String
    Dim rSearch As Range  'range to search
    Dim MyData As Range
 
    Set MyData = Sheet1.Range("A1").CurrentRegion
    If Not UserForm1.TextBox1 = "" Then
        Set rSearch = MyData.Columns(1)
        strFind = UserForm1.TextBox1.Value    'what to look for
 
        With rSearch
            Set c = .Find(strFind, LookIn:=xlValues, LookAt:=xlWhole)
            If Not c Is Nothing Then    'found it
                UserForm1.TextBox2.Enabled = True
            Else: MsgBox strFind & " not listed"    'search failed
                UserForm1.TextBox2.Enabled = False
            End If
        End With
    End If
End Sub

You'll need to determine how to call the procedure based on the structure of your application.
John

**mikerickson** · 11-09-2010, 04:59 PM

Perhaps something like

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = Not (TextBox2.Enabled)
    If Cancel Then MsgBox "Enter a value from column A"
End Sub

Private Sub TextBox1_Change()
    TextBox2.Enabled = IsNumeric(Application.Match(TextBox1.Text, ActiveSheet.Range("A:A"), 0))
End Sub

**rajeev.raj** · 11-10-2010, 12:46 AM

I am unable to do the same, can u please send one example worksheet using above code.

Thanks

**mikerickson** · 11-10-2010, 02:01 AM

I modified the code a bit.
In the attached, press the button and the userform will appear. The second textbox will be visible only if the first textbox has an entry from column A.

Dim Closing As Boolean

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = Not (TextBox2.Visible)
    If Cancel And Not Closing Then MsgBox "Enter something from column A."
End Sub

Private Sub TextBox1_Change()
    TextBox2.Visible = IsNumeric(Application.Match(TextBox1.Text, ThisWorkbook.Sheets("Sheet1").Range("A:A"), 0))
End Sub

Private Sub UserForm_Initialize()
    TextBox2.Visible = IsNumeric(Application.Match(TextBox1.Text, ThisWorkbook.Sheets("Sheet1").Range("A:A"), 0))
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Closing = True
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub