VBA Userform help

Hi can someone help me please I have a userform i have created. I have created a a portal of links to forms, one of the forms i have made using a UserForm (System_Access) and it has Textboxs, 2 comboboxs and a number of checkboxs. Once the form is completed and send is pressed the information needs to be copied to an external sheet (DataSheet) and then sends a email.

The Email works fine but Im having great difficulty with code for the Combobox and coping the textbox, checkbox data across to the DataSheet. It needs to be possible when more than one checkbox is selected then the name of that box is copied to the DataSheet possibly a listbox.

Can anyone help with me with some code to 1) create the combobox 2) copy the info to the Datasheet. I have attached the example of the 2 sheet for reference.

Thank you in advance

Hi Leith

Thank you for your reply and interest.

1)The checkboxs are to select certain types of system access to request for another computer system, its possible to have multiple types. What i wanted is when boxes are checked the checkbox name is copied to the datasheet, maybe into dropdown list. do you think it would be better to do the job multi-select list on the userform that copies to a dropdown list on the datasheet?

2)Once the coding for the userform is complete i will put a working link on the email, the spreadsheet i have attached is a sample of the original one i created.

3)Combobox1 needs to show AA,AO,EO,HEO and Combobox2 needs to show a list of teams, an example is Telephony, Claims and reporting.

4)The code you displayed was when i was trying populate combobox1 by using a defined name called JobRole linked to hidden column U. I couldn't get it to work, i should have deleted it.

Are you able to help?

Hi Leith

Yes a comma separated list on the datasheet sounds a great idea.

Can you help me with coding for the comboboxs, tranfser of data to Datasheet and comma separated list?

Thank you Leith

Leith

Thank you loads for your work on this and working with me, removing the close x is a good idea and i like the look of the buttons.

The items Access for your job role need to populate into another column called Access, before the system item column, it need to work the same way as the system item. I have missed a couple of column,oops. We need a Remove access Yes on the datasheet when the box is ticked on the user form. problem with this is if the remove access is checked the "Access to job role" boxs will not be checked. Also we need a Authoriser grade textbox on the userform under line manager and it will need to copy into the datasheet.

The Datasheet will not be used by the people completing the form and dont really need to see it, so i need the Datasheet to automatic open when the uerform is opened and then close when the userform is closed. i may be able to do this myslef. The datasheet is going to be used by admin to add and update users access.

One more thing could you make the date automacticlly populate with todays date and then copy to the datasheet. or i could remove the userform date field and auto populate the date when the datasheet is opened.

Thank you again, is it possoble to give some one more than one reputation?

Paul

Hello leith
Combobox2 needs to show a list of teams, an example is Telephony, Claims and reporting. And go onto datasheet after the fist combo box data. I have decided to make the user form landscape as not all users have the same resolution and it's possible having the form portrait that the bottom could get chopped, also think it looks better.

Hello leith

I have the spreadsheet working great at work, thank you.

The only things left to do that I can't figure out is the system check boxs. Also I have the datasheet to open automatically when the userform is opened. What I can't work out is for the datasheet to minamize or hide and then save n close when the close button is pressed on the userform. The users who complete the userform do not have any interaction with the datasheet.

Hello Leigh

Thank you, the resize userform code is really clever.

The remove Yes/No needs to only copy the name feilds, Authorizer/Manager name, grade and the date over to the datasheet. How would i amend the code to do that. Would you be able to write a bit code for the userform close button so so when it is clicked the datasheet is closed and post it here.

Hi Leigh

Sorry for not replied until now, I have been on holiday. I have managed to resolve the problem I had and I put a beta version out to a small number of people on a pilot, 2 weeks ago. I took comments today and the users liked it and there was no negative comments.

Thanks for your help with this

Paul