CommandBox Code to update a selected WorkSheet from a UserForm

[NickR1]

Hi,
I have created a logging system using excel and would like to take it one step further. I have been reading about UserForms in excel so I’m very much the novice here, but it’s what I want to do to improve the usability for the excel workbook.
The Wookbook contains 21 worksheets, each worksheet being a different site, the UserForm has a series of Comboboxes and textboxes, The first Combobox contains all the worksheet names using the below code:

Please Login or Register  to view this content.

The remaining comboboxes pull data from a worksheet called data. What I'm after is the code for a CommandBox that will populate the selected Worksheet from the UserForm and all the other data that has been populated using the other ComboBoxes and TextBoxes. I will also require it to find the next available row so it is not over writing the previous data that’s been added.
Can you please help I have tried other code, from other internet sources, but can't seem to get this working.
Cheers

[Palmetto]

Hi NickR1,

Welcome to the Forum.

When reading the Forum Rules, perhaps you overlooked the one that requires the use of code tags?
Before receiving any help, which we're all glad to give, edit your post to add the missing code tags.

Simply hightlight all lines of VBA code then press the button with the # symbol on it.

Thanks.

[NickR1]

HI Palmetto,

Sorry about that, I've added the code tags.

Cheers

[Palmetto]

Here is the basic code example -- see if you can build off this.

Please Login or Register  to view this content.

You may find this useful: Userforms & Controls

[NickR1]

Thank You! I will try and adopt this to my UserForm. I will let you know how I get on.

Thanks.........

[NickR1]

Hi Palmetto,

I have adopted the code you provided to the UserForm I created and adding the data I selected from the Userform to the worksheet that is also select in the UserForm works. Here is the code I amended to what you gave me.

Please Login or Register  to view this content.

I am experiencing an issue though!

One each of the Worksheets in the workbook I have added data validation to 500 rows each cell in the row using data validation but different values, so I can select options like Call Open, Call Closed in one cell and call type like Phone Call, e-mail in another etc. The data validation pulls the data from a separate Worksheet call Data (This is the same for the UserForm comboboxes).

When I select the required data from the UserForm and use the CommandButton Click it populates the Worksheet but it adds the data to the next row \ cells that don't have the data validation configured, so it adds the data to row 501 even though row 2 cells are still blank, and this is where I want the data added, as I haven't populate them with the data validation data.

Is there a way to use the UserForm to populate the Data to the worksheet row \ cells but still have the option to select the values from the data validation on the worksheet when I need to change them?

[Norie]

To to find nextrow use a column that doesn't have data validation.

[Palmetto]

Is there a way to use the UserForm to populate the Data to the worksheet row \ cells but still have the option to select the values from the data validation on the worksheet when I need to change them?

Writing values to a cell using VBA does not alter the data validation. If the cell already has DV applied it will still function correctly after writing a value to it. I may not be clear about what you want. If so, try to be more explicit about what you are trying to accomplish.

[NickR1]

Thanks Palmetto,m, humm that's strange.......can you advise why the userform is adding the data to row 501 and not row 2, is this because there is DV added to the cells in row 1 through to 500?

[Norie]

NickR1

Have you tried using another column to find the next row?

[NickR1]

Hi Norie,

Not following when you mean say “use another column to find the next row”...sorry if I'm being a bit thick...:-(

I have cleared the formatting from column A row 2, thinking because I had a comment in it however when I click the command button it still adds the data select in the userform to row 501, even though there is no data added to any of the cells from column A through to I and row 2 through to 500.

What do you think this is happening is there something I have got wrong in the code?

Please Login or Register  to view this content.

[Norie]

I just mean that instead of looking in column A to find the next row look in another column.

For example to look in column B.

Please Login or Register  to view this content.

If that doesn't help it might be an idea to attach a workbook.

[NickR1]

Hi Norie,


Your suggestion of finding another column (Column J) has enabled it to update the next blank row, however if I use the userform again it overwrites the same row instead of updating the next blank row. I have amended it so the first row is row 4.

Please Login or Register  to view this content.

Any ideas?

Thanks

[Norie]

Attach a workbook?

[NickR1]

Hi Norie,

I will attach the workbook but I will have to reduce the size as its 18MB, I'm out on the road working so I will do this before the end of the week. One other question, how will I attach it other than insert an image??, I can't see an option to attach a file anywhere.

Thanks in advance

Cheers

Nick

[Norie]

Click Go Advanced, click the paperclip in the toolbar and you'll get a dialog where you can select a file to attach/upload.