PDA

View Full Version : Survey in Excel using Visual Basic Need to edit!



snick1982
10-01-2009, 11:30 AM
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
10-01-2009, 11:32 AM
Welcome to VBAX,
You can post a workbook using Manage Attachments in the Go Advanced reply section.

snick1982
10-01-2009, 01:14 PM
There are 22 fields, I tried to upload the image but dont have authority yet..

mdmackillop
10-01-2009, 01:41 PM
Not an image, post a sample workbook.

snick1982
10-02-2009, 11:00 AM
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
10-03-2009, 11:04 AM
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
10-03-2009, 11:20 AM
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
10-03-2009, 01:37 PM
The dropdown data is tied to the userform (http://i26.photobucket.com/albums/c147/Tinbendr/RangeCells.jpg) 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.
ws.Cells(iRow, 1).Value = Me.ques1.Value 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
10-04-2009, 01:52 AM
...but want to conquer it, I dedicated my weekend to this!

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

Bob Phillips
10-04-2009, 01:54 AM
Am I missing something, I cannot see an upload.

snick1982
10-04-2009, 06:22 AM
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
10-04-2009, 06:38 AM
If you can contact that person, do so. If you can't, PM me.

Paul_Hossler
10-04-2009, 06:52 AM
That is a tad optimistic, conquering VBA in a weekend.


20 Years and VBA is still winning :yes

Paul

Bob Phillips
10-04-2009, 07:07 AM
I've got years on you Paul :)

snick1982
10-04-2009, 11:21 AM
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
10-04-2009, 12:58 PM
RE: last messege.

Bob Phillips
10-04-2009, 03:12 PM
I made a very simple entry and the worksheet updated for me with Finish & Exit.

Paul_Hossler
10-04-2009, 07:57 PM
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
10-04-2009, 08:21 PM
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
10-05-2009, 05:29 AM
Thanks guys will be back with the results....

snick1982
10-06-2009, 02:35 AM
What you guys think?

Anything you would do different?

mdmackillop
10-06-2009, 11:04 AM
I've reposted your attachment without the email address. On a quick inspection, exiting without completing the form, aborting the email should not show "Successful" messages.