Hi everyone. I'm having a bit of trouble with a macro I'm using to select certain info.
I don't know much about VBA but I copied a couple old macros I previously had and I've modified them to try and suit the application. It didn't work for me though. (failed miserably actually)
I'm doing up a customer order form for work & its based on our product's price list.
I already have the price list set up with a check box system. Our sales people can check the box for the item a customer wants and then they can select the customized items for the machine etc...
Our sales people want to be able to automatically print off the order form for our clients to sign off with all their selected items without the customer seeing the rest of the price list.
I'm hoping to put automatic borders on it and try and make it look as professional as possible.
I have the basic product information in range p6:r10 (The "P" cells are the description & "R" is it's price). Only one of these rows should be selected by the customer.
I've set up the check box so that when an item is picked the corresponding "P" cell is then visible IE P6<>"" or p7<>"" or P8<>"" etc.
Dim lngRow As Long, rng As Range
If Range("p6") <> "" Then
For Each rng In Range("q6:q10")
If rng.Value <> "" Then lngRow = rng.Row
Next rng
Range("q6:r" & lngRow).Copy
If Range("v6") = "" Then
Range("v6").PasteSpecial xlPasteValues
Else
Cells(Rows.Count, "v").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
Else
MsgBox "Input data is blank - no copy performed!": Exit Sub
End If
Once this is done i wanted to copy the value from cell Q11 and paste it directly after the above info. I wanted to then select this pasted cell along with the cell beside it "W" and make both of them grey with a standard border around the two cells. (This is like a title for some of the customized components to follow)
Here's the tricky bit.
I want to now select all the visible cells from range Q12:R24 and paste it after the title from Q11. This is similar to the first bit. if the p cell is blank then it is to be ignored.
But these cells can be visible in any order whatsoever as multiple items can be selected.
I was hoping to put a border around these cells too but individually on the item description and its cost on the right.
The order form continues like this with more categories but if someone could help me with the above bit I think I could take it from there by changing the cell names.
If anyone can help me with this, I'd be very grateful. Unfortunately I don't know enough (anything) in VBA to be able to do it myself.
I don't know much about VBA but I copied a couple old macros I previously had and I've modified them to try and suit the application. It didn't work for me though. (failed miserably actually)
I'm doing up a customer order form for work & its based on our product's price list.
I already have the price list set up with a check box system. Our sales people can check the box for the item a customer wants and then they can select the customized items for the machine etc...
Our sales people want to be able to automatically print off the order form for our clients to sign off with all their selected items without the customer seeing the rest of the price list.
I'm hoping to put automatic borders on it and try and make it look as professional as possible.
I have the basic product information in range p6:r10 (The "P" cells are the description & "R" is it's price). Only one of these rows should be selected by the customer.
I've set up the check box so that when an item is picked the corresponding "P" cell is then visible IE P6<>"" or p7<>"" or P8<>"" etc.
Dim lngRow As Long, rng As Range
If Range("p6") <> "" Then
For Each rng In Range("q6:q10")
If rng.Value <> "" Then lngRow = rng.Row
Next rng
Range("q6:r" & lngRow).Copy
If Range("v6") = "" Then
Range("v6").PasteSpecial xlPasteValues
Else
Cells(Rows.Count, "v").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
Else
MsgBox "Input data is blank - no copy performed!": Exit Sub
End If
Once this is done i wanted to copy the value from cell Q11 and paste it directly after the above info. I wanted to then select this pasted cell along with the cell beside it "W" and make both of them grey with a standard border around the two cells. (This is like a title for some of the customized components to follow)
Here's the tricky bit.
I want to now select all the visible cells from range Q12:R24 and paste it after the title from Q11. This is similar to the first bit. if the p cell is blank then it is to be ignored.
But these cells can be visible in any order whatsoever as multiple items can be selected.
I was hoping to put a border around these cells too but individually on the item description and its cost on the right.
The order form continues like this with more categories but if someone could help me with the above bit I think I could take it from there by changing the cell names.
If anyone can help me with this, I'd be very grateful. Unfortunately I don't know enough (anything) in VBA to be able to do it myself.
