vba userform loading data to hidden tab

[MikeTE]

hi gyus!

i'm a beginner with VBA and I would really appreciate your help with my code.

I have created form which was supposed to send data to the hidden, password protected tab. when the tab is hidden the form would send data to first sheet under the banner... and that's wrong ;/ is there any way to bypass the "active sheet" rule or application screen.updating? the purpose of this sheet is to create form so everybody can put feedback but won't be able to see other people's feedback meanwhile it would create hidden database for admins to work on.

many thanks
Mike

 

[Belleke]

Hi,

Private Sub CommandButton10_Click() '''add
With Sheets("MASTER")
        iRow = .Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        .Cells(iRow, 1).Resize(, 7).Value = Array(TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, _
        DTPicker1.Value, ComboBox1.Value, TextBox5.Value)
    End With
For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then Ctrl.Value = ""
Next Ctrl
End Sub

 

[MikeTE]

genius! thank you so much that works!!!!

 

[Belleke]

Thanks for the feedback

 

[Belleke]

Hi,
i made a example.
There are 3 sheets, 2 are hidden. One is the master and the 2th one is called lists, and contains the elements for your combobox.
Using Additem to populate a combobox or listbox is very slow, avoid using that.
on the feedback form you will see a little rectangle.
Pushing this rectangle opens the password form.
When the password is correct you kan open the master sheet.
Don't protect the mastersheet and list sheet, just hide them (xlveryhidden) and protect the vba code.
Protect the feedback form.
The password for the password form, the feedback form and the VBAcode is test.
And I changed the characters in the textbox, so nowbody can see the pasword.
And I added a calender, using datepicker is asking for trouble, it is not supported anymore in Office 2016.
Have fun

 

[MikeTE]

that works way better! I was struggling to find a clever way to protect it and hide buttons you made it just perfect! thanks so much

 

[Belleke]

Forgot to mention, if a admin forgets to hide the master sheet, it is closed automaticly when you close the form or the file.
Glad to help.
Cheers.

 

[MikeTE]

hello again,

just a quick question: is there any reason why those forms would work on some computers but not the others(tried them both)? seems like for some people code is tripping on date picker for others on iRow. That puzzles me because I checked that with 5 different users (same machines same systems) and two of them had those errors rest was fine. i'm struggling to understand why some would have error "can't find project or library". I will appreciate any help

 

[Belleke]

Hi,
Press ALT+F11 to switch to the Visual Basic Editor.
On the Tools menu, click References.
Clear the check box for the type library or object library marked as “Missing:”

 

[MikeTE]

the better sheet which you sent pops up with this error. is it possible that different versions or excel may cause this?