none
[Excel] Userform Help RRS feed

  • Question

  • I have a timesheet for my staff that i created and wanted to use userforms to collect their responses. I created the Userform, but am now confused as to how do i manage to pool the responses under assigned head to another worksheet once the staff hits the command button "OK". Also, i'd like this worksheet to be hidden from the the staff as it contains confidential info.

    Link to Onedrive:

    http://1drv.ms/1uhL8tT

    Any help is appreciated

    Monday, January 19, 2015 10:25 AM

Answers

  • Hi

    Try this macro...

    Private Sub CommandButton1_Click()
    Dim lst As Long
        lst = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Sheet2").Cells(lst + 1, 1) = Me.ComboBox1.Value
        Worksheets("Sheet2").Cells(lst + 1, 2) = Me.TextBox1.Value
        Worksheets("Sheet2").Cells(lst + 1, 3) = DTPicker1.Value
        Worksheets("Sheet2").Cells(lst + 1, 4) = Me.TextBox2.Value
    End Sub

    And to Hide your sheet, this will work but you need to password protect your VBA Project and

    select "Lock Project for Viewing" also change the name of "Sheet2" to something only you know.

    These two lines below can be used in the "Immediate Window"

    ThisWorkbook.Sheets("Sheet2").Visible = xlSheetVeryHidden  '>>Don't forget to change the name of the Sheet.

    ThisWorkbook.Sheets("Sheet2").Visible = true  '>>This line to Unhide the sheet.

    Take note : Nothing if safe in Excel, anyone with the will to try will get the hidden information.

    With Sheet2 hidden if you type in Sheet1, any cell "Sheet2!A2 then drag to the right, you will see the information on that row. they can repreduce the full sheet if they want, also if you Google "Excel Password Remover" you will see how simple it is.

    If this anwered your question, please mark the post answered.

    Thanks


    Cimjet



    • Edited by Cimjet Monday, January 19, 2015 7:30 PM
    • Proposed as answer by Cimjet Wednesday, January 21, 2015 12:35 AM
    • Marked as answer by CaillenModerator Tuesday, January 27, 2015 1:10 PM
    Monday, January 19, 2015 7:26 PM

All replies

  • Hi

    Try this macro...

    Private Sub CommandButton1_Click()
    Dim lst As Long
        lst = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Sheet2").Cells(lst + 1, 1) = Me.ComboBox1.Value
        Worksheets("Sheet2").Cells(lst + 1, 2) = Me.TextBox1.Value
        Worksheets("Sheet2").Cells(lst + 1, 3) = DTPicker1.Value
        Worksheets("Sheet2").Cells(lst + 1, 4) = Me.TextBox2.Value
    End Sub

    And to Hide your sheet, this will work but you need to password protect your VBA Project and

    select "Lock Project for Viewing" also change the name of "Sheet2" to something only you know.

    These two lines below can be used in the "Immediate Window"

    ThisWorkbook.Sheets("Sheet2").Visible = xlSheetVeryHidden  '>>Don't forget to change the name of the Sheet.

    ThisWorkbook.Sheets("Sheet2").Visible = true  '>>This line to Unhide the sheet.

    Take note : Nothing if safe in Excel, anyone with the will to try will get the hidden information.

    With Sheet2 hidden if you type in Sheet1, any cell "Sheet2!A2 then drag to the right, you will see the information on that row. they can repreduce the full sheet if they want, also if you Google "Excel Password Remover" you will see how simple it is.

    If this anwered your question, please mark the post answered.

    Thanks


    Cimjet



    • Edited by Cimjet Monday, January 19, 2015 7:30 PM
    • Proposed as answer by Cimjet Wednesday, January 21, 2015 12:35 AM
    • Marked as answer by CaillenModerator Tuesday, January 27, 2015 1:10 PM
    Monday, January 19, 2015 7:26 PM
  • Hi

    I have since altered the excel by not entering it through userforms, but rather via usual excel sheet with a command button to copy data from specific cells to the worksheet. I tried the following command, but for some reason, it would refresh the first row with new data instead of continuing on the next line. Any help will be appreciated

    Private Sub CommandButton1_Click()
    Dim emptyrow As Long
    Sheet1.Activate
    emptyrow = WorksheetFunction.CountA(Range("A:a")) + 1
    ActiveSheet.Cells(emptyrow, 1).Value = Range("date").Value
    ActiveSheet.Cells(emptyrow, 2).Value = Range("Name").Value
    
    'Range("receipt").PrintOut
    
    End Sub
    

    Tuesday, March 10, 2015 8:13 AM
  • Hi

    The script you are showing us is okay, no syntax error in it.

    Check your "Name range". Open your VB Editor with your worksheet, side by side and run your script line by line, using the button "F8".

    You should start a new post with more information.


    Cimjet

    Tuesday, March 10, 2015 1:37 PM
  • I tried running it, but now with the command CountA, it does not fill the immediate next line after the previous one, but rather it overwrites the new data on the same cell. Its just this small trouble thats stopping me from using it in my work. Also, I thought its the same thing that i want to do, so hence I updated my thread instead of typing in a new one
    Tuesday, March 10, 2015 2:47 PM
  • Hi

    I made a sample file with your macro, see link below.

    http://dropcanvas.com/syix2

    I'm not having that problem. If you press the button a few times, you will see that it keeps going down, it does not override the last entry.

    Why create a new post ?

    Because your Post heading is "Userform Help" . To someone trying to help you, It will just bring confusion plus it's mark "Answered".

    See the sample file and reply back.


    Cimjet

    Tuesday, March 10, 2015 5:30 PM
  • Well this is interesting, for me, the data that i want to copy from sheet1 to sheet2 does get copied, but if i enter a new set of data, the 1st set of data gets written over. And i have the exact same code. I'll create a new post, but thanks for the help.
    Tuesday, March 10, 2015 7:04 PM
  • I think I found your problem.

    Do you have any blank rows in between your data ?

    This line:> emptyrow = WorksheetFunction.CountA(Range("A:a")) + 1.

    That will give you the wrong count if you have blank rows.

    Change your code with this one....

    emptyrow = Cells(Rows.Count, "A").End(xlUp).Row
    ActiveSheet.Cells(emptyrow + 1, 1).Value = Range("Date").Value
    ActiveSheet.Cells(emptyrow + 1, 2).Value = Range("Name").Value

    Let me know if it works.


    Cimjet

    Tuesday, March 10, 2015 8:09 PM
  • Well for some weird reason, the code worked the first time, but after the first field was populated, it again went back to doing the same thing of replacing the earlier entry. Also, with the previous code, there wern't any blank fields between the rows in the sheet.

    Further info: I noticed that the line gets added on the 40th row in sheet2, rather than from the start. Could this be a problem

    • Edited by huzzug Wednesday, March 11, 2015 4:37 AM further details added
    Wednesday, March 11, 2015 3:24 AM