?
Solved

Validating A textbox in a UserForm

Posted on 2005-02-25
4
Medium Priority
?
267 Views
Last Modified: 2012-06-27
I have created a User Form that will allow you to enter and search details.  It will then enter these details into a spreadsheet via a save button.  There are three things I would then Like to do

1.  I would like to be able to update details rather than have to press save each time and get a new line
2.  I would like to be able to search and delete a file rather than having to search and manually delete
3.  Finally I have two text fields that allow information to be entered into them but I also before allowing me to press the save   button i need the to validate the field to check that the same information has not already been entered

My Userform has 11 fields (A-K)

A Text Field (This is used to search) This needs to be validated to only allow non duplicate information to be entered
B Text Field (This is used to search) This needs to be validated to only allow non duplicate information to be entered
c Combo Box
d Combo Box
e Option Buttons
f Text Box
g Option Button
h Option Button
i Option Button
j Combo box
k Combo box
   
0
Comment
Question by:LozLaura17
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
manthanein earned 2000 total points
ID: 13404588
OK..  I added  3 more command buttons  cmdAdd, cmdSave and cmdSearch..   I also named the controls according to what field it's referring  to..   then  just paste these code in the form.


Dim strAddress As String

Function FindValue(strFind As Variant) As String
Dim objGroup As Object
Range("A1").Select
Set objGroup = Cells.Find(What:=strFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False)
If objGroup Is Nothing Then
    FindValue = ""
Else
    FindValue = Cells.Find(What:=strFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Address
End If
End Function



Private Sub cmdNew_Click()
    strAddress = ""
    txtA.Text = ""
    txtB.Text = ""
    cboC.Text = ""
    cboD.Text = ""
    opnE.Value = False
    txtf.Text = ""
    opng.Value = False
    opnH.Value = False
    opnI.Value = False
    cboj.Text = ""
    cboK.Text = ""
End Sub

Private Sub cmdSave_Click()
If strAddress = "" Then
    If Trim(txtA.Text) <> "" Then
        Columns("A:A").Select
        strFound = FindValue(txtA.Text)
        If strFound <> "" Then
            MsgBox "Duplicate entry for column A"
            Exit Sub
        End If
    End If
    If Trim(txtB.Text) <> "" Then
        Columns("B:B").Select
        strFound = FindValue(txtB.Text)
        If strFound <> "" Then
            MsgBox "Duplicate entry for column B"
            Exit Sub
        End If
    End If
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
Else
    Range(strAddress).Select
End If
Range("A" & ActiveCell.Row) = txtA.Text
Range("B" & ActiveCell.Row).Value = txtB.Text
Range("C" & ActiveCell.Row).Value = cboC.Text
Range("D" & ActiveCell.Row).Value = cboD.Text
Range("E" & ActiveCell.Row).Value = opnE.Value
Range("F" & ActiveCell.Row).Value = txtf.Text
Range("G" & ActiveCell.Row).Value = opng.Value
Range("H" & ActiveCell.Row).Value = opnH.Value
Range("i" & ActiveCell.Row).Value = opnI.Value
Range("j" & ActiveCell.Row).Value = cboj.Text
Range("k" & ActiveCell.Row).Value = cboK.Text
strAddress = Range("A" & ActiveCell.Row)
End Sub

Private Sub cmdSearch_Click()
Dim strFound As String
   
If Trim(txtA.Text) <> "" Then
    Columns("A:A").Select
    strFound = FindValue(txtA.Text)
    If strFound <> "" Then
        Range(strFound).Select
        txtA.Text = Range("A" & ActiveCell.Row).Value
        txtB.Text = Range("B" & ActiveCell.Row).Value
        cboC.Text = Range("C" & ActiveCell.Row).Value
        cboD.Text = Range("D" & ActiveCell.Row).Value
        opnE.Value = Range("E" & ActiveCell.Row).Value
        txtf.Text = Range("F" & ActiveCell.Row).Value
        opng.Value = Range("G" & ActiveCell.Row).Value
        opnH.Value = Range("H" & ActiveCell.Row).Value
        opnI.Value = Range("i" & ActiveCell.Row).Value
        cboj.Text = Range("j" & ActiveCell.Row).Value
        cboK.Text = Range("k" & ActiveCell.Row).Value
        strAddress = strFound
    End If
ElseIf Trim(txtB.Text) <> "" Then
    strFound = FindValue(txtB.Text)
    Columns("B:B").Select
    If strFound <> "" Then
        Range(strFound).Select
        txtA.Text = Range("A" & ActiveCell.Row).Value
        txtB.Text = Range("B" & ActiveCell.Row).Value
        cboC.Text = Range("C" & ActiveCell.Row).Value
        cboD.Text = Range("D" & ActiveCell.Row).Value
        opnE.Value = Range("E" & ActiveCell.Row).Value
        txtf.Text = Range("F" & ActiveCell.Row).Value
        opng.Value = Range("G" & ActiveCell.Row).Value
        opnH.Value = Range("H" & ActiveCell.Row).Value
        opnI.Value = Range("i" & ActiveCell.Row).Value
        cboj.Text = Range("j" & ActiveCell.Row).Value
        cboK.Text = Range("k" & ActiveCell.Row).Value
        strAddress = strFound
    End If
Else
    MsgBox "Nothing to search "
End If
   
End Sub



Private Sub UserForm_Initialize()
strAddress = ""
End Sub
0
 

Author Comment

by:LozLaura17
ID: 13415347
My save function will take all the values that are entered into the userform and put them in a spreadsheet. I now need to write a function that will allow me to use my search function (search by column A OR B and then show rest of information from that row into the userform) to get up the current information on that record and then edit some of the information and resave those detail.  At the moment all it will do is enter these details as a current row or say that they are duplicate
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13445440
The only thing I can think of with regards to not duplicationg searches is to use a combobox and there is an option in the propertys that will not allow duplicate values to be in there :) Not sure if that helps you any ?
0
 
LVL 7

Expert Comment

by:manthanein
ID: 13465610
hi LozLaura17  have you tried my code.  I actually  made the search and save capabilities working in there
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
New style of hardware planning for Microsoft Exchange server.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question