How to populate multiple cells from multiple checkboxes from a VBA Form in Excel

[veresistvan]

Hi ,

I have created an excel workbook where with the help of the form you can easily create a fabrication quote.

I have a pre styled section on the worksheet which at the end will be printed on an A4 sheet together with the quotes to what I need some help

on the form I have 10 items which can be added to the quote by checking the checkbox.. this then will show it price on the form and allow to chose the quantity.. this is all done but I am having difficulties to move the checked items to the worksheet.

My allocated space would be from line 31 to 63 using every second line

I would like to move the information from the form to the sheet in a way that if I uncheck one of the previously checked items to remove it from the sheet as well fore more it will move the following checked items on line up so I am not leaving gaps

Do I have to write the code on the checkbox or should generate a module instead?

If you have any idea please let me know.

[torachan]

A lot easier for us to work with your actual workbook - see big yellow banner at head of page - remember to de-sensitise any data.
Seems a simple request on the face of it - I will lookout for your further posting.
torachan.
Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[veresistvan]

Please find attached the workbook and screenshots of what I would like to achieve on that particular selection

[torachan]

seperate module - loop through the check boxes - put the true checkbox values directly into sheet in sequence - ignore false checkboxes in the loop.
note; in present checkbox code you read from sheet1 and then unnecessarily pass the value back to sheet1.
the actual print area also need setting in pdf at the moment it only defines the save area.
if stuck shout again.
torachan.

[veresistvan]

Yes.. i was sending back the data to sheet1 then reading that data from sheet2, this is because I had some conflict issues of the data sent to the sheet messing up the cells currency format..

Now I am not 100% sure how can I formulate a loop statement that can handle all checkboxes and data to be read from sheet1.. I need a starting point.

Should the codding be behing each checkbox or I should have a module created then refer to it from each checkbox.. sorry I am beginner in this

[torachan]

Attached much modified code.
To aid the compressing of code there is a dummy hidden textbox (QB16) - it enables a continuos loop to be used.
QB16 is visible in the form development window and is hidden at runtime.
Also iterated out 3 or 4 lines on the 'Fabrication' button just to enable moving between forms.
Happy to answer any questions although I think the flow/code is fairly straightforward.
I have put the sheet loading loop in the 'Save pdf' key immediately before the save routine.
torachan.

[veresistvan]

Yes, This looks exactly what I was looking for, I just need to be able to transform the prices back to currency so The sheet formula can sum them up. Thank you for your help, I will put the code in my final application to see if is all ok and will get back if other question rises regarding this.
I would love to understand more about the controls ( is this regarding the selection of quantity and x for the number of checkboxes? .. also you set up z as a long variable but where have you used it and for what? ** thank you for explaining )

[torachan]

Ignore the 'z' Dim declaration (you can delete it) left over from initial attempt at sequencing code.
Fortuneatly you had refrained from giving your CheckBoxes/ComboBoxes/TextBoxes fancy individual names.
Each group having the same numerical suffix, this made looping through easy rather than dealing with 17 sets of data transfer.
Normally I do not bother to change the default control name (e.g. TextBox1) just make a written list whilst developing (annotate relevant controls, if your memory is has bad as mine).

[veresistvan]

I have done the changes and all works fine except for one thing.. I cant sum up the populated cells with the prices. It gives me a green triangle in the cells saying it is a text format so I was trying to transform the cells from VBA with

Sheet2.Range("J31:J63").Style= "Currency"
' also tryed with .Numberformat

or

Sheet2.Range("J31:J63").Value= Format(Sheet2.Range("J31:J63").Value, "£#,##0.00")

I have no luck.. it shows me in currency but at the bottom of the page where should be the total, I left the code =Sum(J31:J63) which won't add them up.


Is there any solution to this?

[torachan]

post your workbook so that we are looking at the same hymn sheet.

[veresistvan]

I have attached the document with a screenshot of the result..

[torachan]

You appear to have removed the summation code ?????
There should be no on sheet formula - this was done in the code as was the formatting.
Remove formula from sheet and paste back the code below.

Please Login or Register  to view this content.

[veresistvan]

This works in the test document but as I changed into my original workbook it has a glitch that doesnt add up the 2 results but writes them next to each other in the cell.

i tried to change the the way that the first value appears on sheet by sending its value to another sheet and copy it to the final sheet from there.
Do you have an idea why is this happening? please see image attached

[torachan]

without the actual workbook it would be guesswork - desenstise data and upload the workbook should then be easy to fix.

[veresistvan]

I Have attached the simplified workbook..

' I had overwritten the X in the corner of the windows so no-one else from the company can enter to the coding part.. therefore the only way i am able to go to developer mode is opening an empty excel file and entering developement mode then opening this workbook and stopping it from development mode



So my problem here is between the customised which now works fine and the stocked prices where the overall prices are added next to each other...

** another question I have is that.. is there a way to return from the fabrication page with the back button to the initial page...?

[torachan]

Paste the code below over existing final print.
Cell addition appeared to concatenate so I have attributed the values to variables for the addition.

If you want to break into your program there is no need to open another empty workbook
Hold down 'Ctrl' and press 'Pause/Break' this usually takes you straight to the code editor.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[veresistvan]

I will try this.. also thank you for the shortcut..

for the back button as both.. customised uses the same fabrication sheet as the stocked stones.. so if i would go through the customised section to fabrication and press back.. this code will end up taking me back to the stocked stones