Option Buttons

[JP801]

Hey everyone,

First I have to say I am very new to excel and spreadsheets and have no idea how to get this to work or if it's even an option to create something like this.

What I am trying to do is create a form that has two option buttons. If either of the buttons are chosen, it will populate entry fields or a section below. I have provided a screen shot of what I am hoping to accomplish. Let me know if you have any questions.

Thanks everyone!

 

[Micron]

Shouldn't be too hard to figure out. What are those pics of - userform or sheet? You already have controls on one or the other?
Then are they activeX controls or ms forms controls?

If the pics are of a sheet, why not just show one view or the other by default? You'd have a 50/50 chance of it being the right one. If not, then the other button selection would change the cell content; e.g. the word "item" changes to "mileage". Much easier than trying to hide/show things. However, it's not clear how much needs to change. What if the user needs extra rows to allow more items than you've shown?

 

[JP801]

Shouldn't be too hard to figure out. What are those pics of - userform or sheet? You already have controls on one or the other?
Then are they activeX controls or ms forms controls?

If the pics are of a sheet, why not just show one view or the other by default? You'd have a 50/50 chance of it being the right one. If not, then the other button selection would change the cell content; e.g. the word "item" changes to "mileage". Much easier than trying to hide/show things. However, it's not clear how much needs to change. What if the user needs extra rows to allow more items than you've shown?

Hey Micron! Thanks for reaching out!

This is a sheet. I Just removed the grids. (Sorry for the confusion) The screen shot with the Date/Expense/Amount is an example of what I would like to populate if the "Item" button was selected. The "Mileage" option will populate different entry fields such as Date/Description/Miles/Amount.

I want the end user to be able to switch back and fourth between the two options incase they need to fill out both areas to get an amount. The two amounts do not need to be added together as our accounting team wants them separate.

The buttons were created through ActiveX and that is about as far as I've gone as I am confused on how to approach this at this point. If you think there is a better way of creating this form, please let me know!

The end user should not need to add anymore rows are we have done research on previous reimbursements within our company and it rarely exceed more then 5 items at a time.

Hope this helps paint a picture a bit better. Sorry for the lack of detail.

 

[Micron]

No apologies necessary.

The screen shot with the Date/Expense/Amount is an example of what I would like to populate if the "Item" button was selected.

I get that, but what I don't get from the pics or explanation is where these sections are. That is, there's no grid (row/column) info, no tab info.
So you have 2 forms, one for each type of expense? Then I don't understand the selection approach.
Or you have one sheet that you're using as a form for both types? That would mean you're keeping the data in another sheet - or in a sheet for each type?
In that case it would make sense to me to just alter the cell text as I said since the layout would be the same and expense type seems to be the only difference. That way, code or formulas could always refer to the same range and put the data on the correct sheet based on what the cell contains as the expense type. That type would be altered by your option button code. Of course, that assumes the cell only contains the word for the expense type. If it contains the entire phrase that you show, then it would be based on finding "item" or "mileage" in that string.

I'm not nearly as savvy with Excel and its VBA as I am with Access, but I help here where I can. My level of experience here leads me to think that userforms with ActiveX controls are much more versatile, more intuitive and probably safer to use than sheet forms. Users cannot delete controls from a userform, but they sure can screw up a sheet form unless you implement protection of some sort. Much easier to protect design with a userform IMO.

 

[JP801]

I figured it out. Thanks for willing to help out Micron!

 

[Micron]

I think it's customary to post your solution when it is your own in case it helps someone else in the future. You can also mark your own post as the solution.

 

[JP801]

Here is the code I used:

Private Sub OptAdd_Click()
Sheet2.UsedRange.Copy
Sheet1.Range("C11").PasteSpecial xlPasteAll
End Sub

Private Sub OptEdit_Click()
Sheet3.UsedRange.Copy
Sheet1.Range("C11").PasteSpecial xlPasteAll
End Sub

This code also applies if you are trying to paste a specific range and not the whole sheet.

Private Sub OptAdd_Click()
Worksheets("Sheet3").Range("A1:L45").Copy
Worksheets("Sheet1").Range ("A11:L54")
End Sub