• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vba userform loading data to hidden tab

MikeTE

New Member
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
 

Attachments

  • vba test form - Copy.xlsm
    864.3 KB · Views: 4
Hi,
Code:
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
 
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
 

Attachments

  • Feedback form.xlsm
    65.1 KB · Views: 6
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
 
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.
 
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
 

Attachments

  • pic.png
    pic.png
    53.4 KB · Views: 4
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:”
 
the better sheet which you sent pops up with this error. is it possible that different versions or excel may cause this?
 

Attachments

  • Untitled.png
    Untitled.png
    44.4 KB · Views: 5
Back
Top