Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Validating A textbox in a UserForm

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
LozLaura17
Asked:
LozLaura17
  • 2
1 Solution
 
manthaneinCommented:
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
 
LozLaura17Author Commented:
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
 
gecko_au2003Commented:
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
 
manthaneinCommented:
hi LozLaura17  have you tried my code.  I actually  made the search and save capabilities working in there
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now