Survey in Excel using Visual Basic Need to edit!

[snick1982]

Hi there

Trying to edit a survey that was made by someone else in work who has now left. I am learning as I am going, need some advice / help to understand how to change the names of some of the fields and also change some of the multiple choice names.

Am a novice but have figured out how to change the names however when data is entered it doesnt send it to the spreadsheet and when you click one of the buttons to save enterd data onto the worksheet it gives this response:

The collection , object, or user-defined type doesn't contain the referenced member This error has the following causes and solutions:

• You misspelled the object or member name. Check the spelling of the names and check the Type statement or the object documentation to determine what the members are and the proper spelling of the object or member names.
• You specified a collection index that's out of range. Check the Count property to determine whether a collection member exists. Note that collection indexes begin at 1 rather than zero, so the Count property returns the highest possible index number.

Can share the survey if you would like to look.

Thanks

N

[mdmackillop]

Welcome to VBAX,
You can post a workbook using Manage Attachments in the Go Advanced reply section.

[snick1982]

There are 22 fields, I tried to upload the image but dont have authority yet..

[mdmackillop]

Not an image, post a sample workbook.

[snick1982]

Hi there,

I am posting the spreadsheet to articulate what I am trying to do better.

Basically some fields need changing, some of the subcategories also need changing and some fields need deleting.

I have done this in VBA, just by deleting on the 'user forms', however then when re-opening it, the survey section does not send any information to the worksheet.

I am probably doing several things wrong as I used VBA for the first time two days ago, but want to conquer it, I dedicated my weekend to this!

Any help is really appreciated guys

N

[Tinbendr]

Basically some fields need changing, some of the subcategories also need changing and some fields need deleting.

Could you be more specific? Which fields and what do you need done to them.

... the survey section does not send any information to the worksheet.

I couldn't duplicate this. Is there a specific field that doesn't make it to the sheet?

[snick1982]

Could you be more specific? Which fields and what do you need done to them. I couldn't duplicate this. Is there a specific field that doesn't make it to the sheet?

I need to change the names of the fields on the questionare and their related field on the worksheet.

There is no problem with the survey that I have uploaded, I basically need a step by step process as to how this would be done and how difficult it would be.

Can anyone help?

[Tinbendr]

The dropdown data is tied to the userform via ranges specified in the userform. If you want to change it, you'll have to have access to Sheet3. To view Sheet3 from the Editor, right click on Sheet3 and select View Object. The color is all blue. I'm assuming if you change the background color to None, or text color Black, then you'll be able to see the dropdown data.

The labels on top of the text/combo boxes in Userform1 are Labels that correspond to the Column in Sheet1. If you change the Label, you should also change the Column Header on Sheet1. This is aesthetic change, but an important one if you will be viewing the data later.

If you add/remove a question, you'll have to do the same in Userform1, under the CommandButton1_Click to store/retrieve the data based on the column.

So the line looks like this.
[vba]ws.Cells(iRow, 1).Value = Me.ques1.Value[/vba] The number one (1) in Cells(iRow, 1), corresponds to the Column of the sheet. You'll just need to coordinate which question is to go in which column. You'll need to change it in the CommandButton2_Click sub also.

[Bob Phillips]

...but want to conquer it, I dedicated my weekend to this!

That is a tad optimistic, conquering VBA in a weekend.

[Bob Phillips]

Am I missing something, I cannot see an upload.

[snick1982]

Thank you so much for the help, this seems pretty straight forward..

I did try to get rid of the background colour however it was locked also the columns on sheet 1 are also locked, I tried to unlock this but it asked for a password...

Is this something I am doing wrong or will I need to get the password from the original creator of the survey?

Thanks in advance..

[Bob Phillips]

If you can contact that person, do so. If you can't, PM me.

[Paul_Hossler]

That is a tad optimistic, conquering VBA in a weekend.

20 Years and VBA is still winning

Paul

[Bob Phillips]

I've got years on you Paul

[snick1982]

ok so have taken the password off and deleted the blue background, then selected all and turned the font colour to black but there is still nothing visible...

[snick1982]

RE: last messege.

[Bob Phillips]

I made a very simple entry and the worksheet updated for me with Finish & Exit.

[Paul_Hossler]

I think you should go into Options and show Sheet Tabs (don't remember how to to it on 2003), and Show Row / Column headers (at least until you sort it out)

Also on 'Results' unhide columns AF - AS in order to update the form lookup values, etc.

On 'Survey' there is a blue text box covering the cells

Paul

[Tinbendr]

OK, found the combobox data. I thought it was on Sheet three, but it's in a group of hidden columns at AP. Goto sheet 1, scroll until you see AE. Then you'll notice the column jump to AT. Select AE and AT, then Format->Columns->Unhide. That is the data in the combobox.

Back to Userform1, SHA combobox (ques1), looking at the Rowsource property, you will see the range pointing to the data on Sheet1.

When you change a question on the userform, then the corresponding values are in this range of columns.

This takes care of the comboboxes.

The data of the textboxes are entered by the user and stored on the sheet along with the comboboxes.

[snick1982]

Thanks guys will be back with the results....