Need help tidying up code

[robhall1966]

Hi Can anyone help me tidy up some code, the current code works but is difficult to maintain, I'm sure this can be simplified but being a novice I am unsure, any help would be appreciated.


The current code is only 3 blocks, I have around 20 blocks of the same code in total.

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer



Kindest regards

Rob

[Greg M]

Hi there,

It's difficult to suggest/test a proposed solution without access to your workbook, but it looks as if you are performing multiple Copy And Paste operations on blocks of data which are spread uniformly across your worksheet - if that is the case, the following code might do what you need:

Please Login or Register  to view this content.

The highlighted values may be changed to suit the layout of your worksheet.

These rows have been added to the original post.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[Mumps1]

Try: (Untested)

Please Login or Register  to view this content.

[robhall1966]

Many thanks Greg, this does appear to work, can I just ask what the offset is there for? i.e.

Please Login or Register  to view this content.

I had to set this to zero as opposed to -1 to get it to work.

Thanks
Rob

[robhall1966]

Hi Mumps1

This also works for me, as does Gregs, I had almost 30 blocks of my own code to maintain, this has made it much easier, I knew a loop would be the answer but still getting to grips with VBA.

Thank you both so much for your help here

Rob

[Mumps1]

Glad to help.

[robhall1966]

Just getting my head around loops, I have some error checking code in the same program, there are 3 text boxes that if one has a value then all 3 must have a value, not sure if this can be optimised with a loop?

Please Login or Register  to view this content.

Rob

[Greg M]

Hi again Rob,

Many thanks for your very prompt feedback and also for the Reputation increase - much appreciated!

You're quite right - my bad The line should of course read:

Please Login or Register  to view this content.

In my defence I'll say that I'd probably have spotted that error if I'd had a copy of your workbook to test it on!

Just heading out now but will try to take a look at your latest query when I'm back.

Best regards,

Greg M

[Greg M]

Hi again Rob,

See if the following code does what you need:

Please Login or Register  to view this content.

The highlighted values may be altered if the number of windows or the names of your controls are ever changed.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[robhall1966]

Thanks for your help once again Greg,

I will give this a try when I'm back in work on Tuesday, will let you know how I get on.

Rob

[Greg M]

Thanks for that - I look forward to hearing from you next week.

Greg M

[robhall1966]

Hi Greg

I have tested the code you kindly provided, check on sash 1 as an example, the code doesn't appear to check for the qty when data is entered into Txt_Sash1_Width And Txt_Sash1_Height, also I need the code to stop running when conditions are not met, so the user can correct the error, it may be worth noting that my current (untidy) code allows for Txt_Sash1_Width And Txt_Sash1_Height and Combo_Sash1_Qty to be empty.

Each block i.e. sash1, sash2 and sash3 need to be checked independently.

I realise you only have a small snippet of the program and appreciate your help.

Kindest regards
Rob

EDIT:

I have an error check for the quantity which I believe was missing from the original post

Please Login or Register  to view this content.

[Greg M]

Hi again Rob,

Thanks for your latest feedback - sorry about missing the Quantity check.

See if the attached workbook does what you need - it uses the following routine for data validation:

Please Login or Register  to view this content.

Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

[robhall1966]

Thanks Greg

The sample workbook you very kindly attached works great, however I am unable to get this to work within my code, I have attached my form which will give you a better overview, the error message should only appear if there has been some text entered into sash1,2 or 3, these all work independently of each other, as per your sample form, the code should also stop after clicking ok on error.

Thank you for all your help on this, it is much appreciated.

Kindest regards
Rob

[Greg M]

Hi Rob,

Many thanks for posting your workbook.

With luck, I hope to be able to post something here tomorrow.

Regards,

Greg M

[robhall1966]

Much appreciated Greg,

Rob

[Greg M]

Hi again Rob,

Sorry I didn't manage to post this yesterday - busier day than anticipated!

Anyway, I think the following is what you need for the Cmd_NewWindow_Click routine:

Please Login or Register  to view this content.

I've been playing around with the workbook you posted and have taken the liberty of rewriting parts of it. I hope you'll agree that the attached version of your workbook is a bit simpler and a bit more structured than the original. You can see how adopting a consistent style for naming the controls on your UserForms makes it easier to keep track of them, and also significantly simplifies processing controls when using VBA Code.

Some of the items you might care to look at in the DataWF UserForm code include populating the dropdown lists of the ComboBoxes, handling the CheckBoxes in the Add-On frame, allowing only numeric input in the dimensions TextBoxes, and how the use of the CounterCell property of the UserForm decouples the UserForm VBA Code from the workbook/worksheets - i.e. the UserForm does not need to "know" either the worksheet or the address of the Counter cell on the Dashboard worksheet.

Your project was an interesting one for me (which is why I enjoyed playing around with it), and if you feel that I might be able to give you some more assistance with it you can send me a private message and we can continue via email - if not, please feel free to ask me here about any aspect of the code which you do not understand.


Hope the above helps - as always, please let me know how you get on.

Regards,

Greg M

[Greg M]

Hi Roy,

Many thanks for your private message regarding the above.

I look forward to working further with you on this project.

Regards,

Greg M

[AliGW]

Administrative Note:

All questions and answers will benefit other posters when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

For this reason, please do not take any of this discussion to private messages. Everything should be shared here. Thanks.