Passwords and hidden object in a userform

[phil5529]

Hi there. i am working for a non-profit agencie and am making them a form in my spare time but have become quite syuck based on my feeble VBA knowledge.

I have created a huge userformwith about 15 pages to use as an application form. As it gets filled in so do the worksheets/printable version. I have managed to link all textboxes combo and check boxes to the worksheet but have a three questions and was hoping someone could help.

1.)I need code that will bring up the correct hidden worksheets when the form is submitted dependent on a check box. for instance when you hit submit and 'CheckBox347' is checked it brings up worksheets 1 2 and 3 but when 'checkbox 348' is checked it brings up sheets 1, 2, 3 and 4.

2.) i want some sort of password automated autocomplete. i have a database with lots of peoples information on, is there a way to auto complet some peoples data if they type in a particular username and password.

and 3) the easier question, i want a another checkbox to appear when one checkbox is ticked. to prevent someone entering info when it is not needed.

I know this is a hell of a lot to ask but i though i would ask all at once instead of annoying people by getting one question answered then asking another.

i thank anyone who reads this for taking time and hope you can help me.

 

[ck1999]

Question 1

if checkbox347.value = true then
sheets("sheet1").visible = true
sheets("sheet2").visible = true
sheets("sheet3").visible = true
end if
if checkbox348.value = true then
sheets("sheet1").visible = true
sheets("sheet2").visible = true
sheets("sheet3").visible = true
sheets("sheet3").visible = true
end if

Question 3. Make a checkbox that has the visible property false. then
if checkbox1.value = true then
checkbox2.visible = true
else
checkbox.value =false
checkbox.visible=false
end if

ck1999

I would make on workbook close to have all sheets go back to xlveryhidden.

 

[phil5529]

Thankyou very much thats great.

Seemed quite simple, i feel quite silly now.

any ideas on question 2, is it actually possible

Many Thanks

Phil

 

[xlbo]

No. 2 should certainly be possible - exactly how depends on the setup of your form and the data behind it

sample syntax

Textbox1.Text = sheets("Data").range("A1").value



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[phil5529]

Hi XLBO.

Okey, this question is going to really display my lack of knowledge.

How can i show you the setup and the data behind the form for you to tell me if it is possible.

I understand that syntax but i need an option to type in a username and password, and when you type in a different username, you get a different value in the box

Many thanks for replying.

Regards

Phil

 

[xlbo]

How does the username relate to the data ?

Is the data all held in 1 row with the user name as part of the data ?

If so, this should be fairly easy

If the data is related to username, you would use the FIND method to get the row of data to use and thn simply reference the cell(s) on that row you wish to use to populate the textbox(s)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[phil5529]

Ah right.

In the columns i have titles such as username, password, first name, first line address etc.....

so each row is a new person.

So column A is username and B is password, the sheet is called "data1".

The following columns (C-AM) is just client information.

Many Thanks

Phil

 

[xlbo]

The exact way of doing it depends on the layout of your form but in essence, the structure you need would be:

Collect Username from textbox
Collect password from textbox

Check password against list of passwords
If verified Then
   use FIND method to fin the username in colA
   eg.
    Set fCell = Sheets("Sheetname").Columns(1).Find(Username)
   Obtain the ROW of the found cell
   e.g. theRow = fCell.row

   pick up relevant data from sheet
   e.g. Textbox2.Text = Sheets("Sheetname").cells(theRow, ColumnNumber)
Else
 msgbox "Incorrect Password"
End If

There should be plenty of examples in the archives here for using the FIND method and for referencing specific cells in a worksheet

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[phil5529]

Ill give that a try thank you very much Geoff

Ill let you no later how it goes!

Regards

Phil

 

[phil5529]

Phwew. it's taken a day, i used your code thank you soooo much, i came up with this, it works pretty well, what do you think



Private Sub Find_First_Click()

Dim FindString As String
Dim Rng As Range
FindString = TextBox1.Value
FindString = TextBox2.Value
If Trim(FindString) <> "" Then

With Sheets("Data1").Range("B6:C270")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then

DecisionInPrinciple.TextBox4.Text = Sheets("Data1").Cells(Rng.Row, 18)
DecisionInPrinciple.TextBox8.Text = Sheets("Data1").Cells(Rng.Row, 5)
DecisionInPrinciple.TextBox9.Text = Sheets("Data1").Cells(Rng.Row, 10)
DecisionInPrinciple.TextBox7.Text = Sheets("Data1").Cells(Rng.Row, 11)
DecisionInPrinciple.TextBox6.Text = Sheets("Data1").Cells(Rng.Row, 12)
DecisionInPrinciple.TextBox5.Text = Sheets("Data1").Cells(Rng.Row, 13)
DecisionInPrinciple.TextBox358.Text = Sheets("Data1").Cells(Rng.Row, 14)
DecisionInPrinciple.TextBox357.Text = Sheets("Data1").Cells(Rng.Row, 15)
DecisionInPrinciple.TextBox11.Text = Sheets("Data1").Cells(Rng.Row, 7)
DecisionInPrinciple.TextBox12.Text = Sheets("Data1").Cells(Rng.Row, 9)
DecisionInPrinciple.TextBox13.Text = Sheets("Data1").Cells(Rng.Row, 17)
DecisionInPrinciple.TextBox9.Text = Sheets("Data1").Cells(Rng.Row, 10)
DecisionInPrinciple.TextBox10.Text = Sheets("Data1").Cells(Rng.Row, 3)
DecisionInPrinciple.TextBox359.Text = Sheets("Data1").Cells(Rng.Row, 6)

Application.Goto Rng, True
DecisionInPrinciple.Show
Unload Me
Else
MsgBox "We did not recognise your entry"


End If
End With
End If

End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub TextBox2_Change()

End Sub

Private Sub UserForm_Click()

End Sub



Thanks again.

Phil

 

[xlbo]

Nothing particularly wrong with the code except that you apply both Textbox1 and textbox2 values to "Findstring" - coupla bits that could be tidied up for less typing:

Private Sub Find_First_Click()

    Dim FindString As String
    Dim Rng As Range
    Dim Frm as UserForm

    FindString = TextBox1.Value
    FindString = TextBox2.Value ???????
    If Trim(FindString) <> "" Then
        
      With Sheets("Data1")
            With .Range("B6:C270")
              Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole)
              If Not Rng Is Nothing Then
             End With

           Set Frm = Forms("DecisionInPrinciple")

            Frm.TextBox4.Text = .Cells(Rng.Row, 18)
            Frm.TextBox8.Text = .Cells(Rng.Row, 5)
            Frm.TextBox9.Text = .Cells(Rng.Row, 10)
            Frm.TextBox7.Text = .Cells(Rng.Row, 11)
            Frm.TextBox6.Text = .Cells(Rng.Row, 12)
            Frm.TextBox5.Text = .Cells(Rng.Row, 13)
            Frm.TextBox358.Text = .Cells(Rng.Row, 14)
            Frm.TextBox357.Text = .Cells(Rng.Row, 15)
            Frm.TextBox11.Text = .Cells(Rng.Row, 7)
            Frm.TextBox12.Text = .Cells(Rng.Row, 9)
            Frm.TextBox13.Text = .Cells(Rng.Row, 17)
            Frm.TextBox9.Text = .Cells(Rng.Row, 10)
            Frm.TextBox10.Text = .Cells(Rng.Row, 3)
            Frm.TextBox359.Text = .Cells(Rng.Row, 6)
            
                Application.Goto Rng, True
                Frm.Show
                Unload Me
            Else
                MsgBox "We did not recognise your entry"
                
                
            End If
        End With
    End If
    
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question