Userform Creation - Checklist Yes/No with Comments

**Beefmaster7337** · 11-12-2012, 10:15 AM

Good afternoon,

I am new to the VBA side of Excel and have a tiny understanding of VBA. I currently have a problem in creating a list of questions with a Yes, No or N/A answer and then having a comments box at each question if required.

What I want to do is have a basic front page with several buttons, one to open the checksheet, and one to open the raw data. (From that raw data I will produce reports/graphs)

I have a bit of code within a userform as follows:

'Used to put data into sheet 2

RowCount = Worksheets("DATA").Range("B1").CurrentRegion.Rows.Count
With Worksheets("DATA").Range("B1")
.Offset(RowCount, 0).Value = TB_PART_NUMBER.Value
.Offset(RowCount, 1).Value = TB_FAIR_REF.Value
.Offset(RowCount, 2).Value = CB_MEP.Value
.Offset(RowCount, 3).Value = DateValue(TB_DTE.Value)
.Offset(RowCount, 4).Value = CB_REGION.Value
.Offset(RowCount, 5).Value = OP_1.Value
.Offset(RowCount, 6).Value = OP_3.Value
.Offset(RowCount, 7).Value = OP_5.Value
.Offset(RowCount, 8).Value = OP_7.Value
.Offset(RowCount, 9).Value = OP_9.Value
.Offset(RowCount, 10).Value = OP_11.Value
.Offset(RowCount, 11).Value = OP_13.Value
.Offset(RowCount, 12).Value = OP_15.Value

End With

Now this is putting the data where I would like it but I can only access that currently when I am in the userform and press play on the macro. I would like that code to run when a button is pressed from the front sheet.

I don't suppose someone has a simple sheet which I could modify/copy...

If I need to explain any better please state and I will try to.

Regards

**Dave H9** · 11-12-2012, 11:27 AM

You Should add a button to your userform and call your subroutine from that

Please Login or Register  to view this content.

Hope that helps

Dave H

**Beefmaster7337** · 11-12-2012, 11:37 AM

Hi Dave,

Thanks for that,

I managed to get there in the end but now I have a right problem with long code checking 100 option buttons, I want to check the buttons are all true and if so populate my database if any of them are false it needs to pop a promt up. I started putting code out in long as shown but I don't think this is the right idea at all.

If OP_1.Value = "True" Then
If OP_3.Value = "True" Then
If OP_5.Value = "True" Then

**Dave H9** · 11-12-2012, 11:44 AM

You could shorten with "OR"

Please Login or Register  to view this content.

Dave H

**Beefmaster7337** · 11-12-2012, 12:05 PM

Hi Dave,

That would help if it was only 5 options for example but I got 150 options so I was thinking is there not a possible way of doing it like so:

Formula:

Please Login or Register  to view this content.

**Dave H9** · 11-12-2012, 12:50 PM

Ok will have a look and get back to you tomorrow....if no one else has.

Cheers

Dave h

**Dave H9** · 11-13-2012, 03:50 AM

Two ways spring to mind -

1. Add a helper sheet and have your userform write true or false in a column as the data is entered. you can then use a for each i.....next i to check for false

2. Create the long hand code in a spreadsheet using formaula and concantenate, then paste into your code (as text).

Hope that helps, if not post an example and I'll try and sort it out.

Dave H