A little (?) VBA help

[Mike_H]

Hi, A task I was looking at in my spare time as a favour has suddenly become part of my annual performance review and I cannot quite get it finished. The program is to be used for people to assess thier computer systems fitness for purpose and compliance with ITIL. The overview is that customers open the document and are presented with a form that has a number of requirements and each requirement has three results.

Customers must enter the application name then work through the form and determine the systems compliance state. at the end the program calculates an overall score and gives the results.

The user then saves the form away from a button press that uses the name entered at the start as the form name

In the main the result I have works as advertised but there are a few "niggles" I cannot resolve. Probably looking to hard. I am not an experienced programmer and most of what I have is gleaned from other resources.

The missing bits are

When the process starts it shows the background data that is used to populate the form. Can I hide this from the customer?

When I start the process the question seems to repeat the first question but puts answer into the second question, from there it is always one step out.

As the form scrolls through the form I want the bottom three columns to show the URL of the relevant documentation for each section.

When I save the form away it saves the scripting as well so that the next time it opens it opens with the form re-enabled as though it is going to start a new record. I need it to save in such a manner that it is a plain form when re-opened

Are any of these steps possible?

[lucas]

When the process starts it shows the background data that is used to populate the form. Can I hide this from the customer?

Add a sheet and force the workbook to open to the blank sheet:

add to workbook open procedure:

[vba]Sheets("Sheet2").Select[/vba]

When I save the form away it saves the scripting as well so that the next time it opens it opens with the form re-enabled as though it is going to start a new record. I need it to save in such a manner that it is a plain form when re-opened

Save the sheet to a new workbook and close the original without saving changes.
[vba]Sub SaveSales()
Application.ScreenUpdating = False
Worksheets("Sales").Copy
ActiveWorkbook.SaveAs "F:\Temp\Sales.xls"
ActiveWorkbook.Close
Application.ScreenUpdating = False
End Sub[/vba]

[vba] Application.Quit[/vba]

[Mike_H]

Thanks Lucas I'll give it a go tonight

Mike

[lucas]

Ok Mike, You will need to give better info on these questions:

When I start the process the question seems to repeat the first question but puts answer into the second question, from there it is always one step out.

As the form scrolls through the form I want the bottom three columns to show the URL of the relevant documentation for each section.

[Mike_H]

rats thought I had covered all bases.

When the form is opened and the form activates, the user fills in the Application Name and the focus should move to the the first GOVERNANCE CRITERIA (Application Strategy). The user selects the relevant COMPLIANCE STATUS radio button that corresponds to the criteria that agrees with the current state of the application for that criteria.

They then click the NEXT CRITERIA button which should move them to the next GOVERNANCE CRITERIA but the form details do not update. However, the underlying field captures the status of the radio button on and records it on the second COMPLIANCE STATUS row. Clicking the NEXT CRITERIA button shows the Second GOVERNANCE CRITERIA but updates the third COMPLIANCE STATUS.

The second part is that when the form moves between GOVERNANCE CRITERIA VIEWS. The three coloured boxes at the bottom will show hyperlinked URL that takes the user to the location of the document that fully explains the particular elements of the

Hope that explains things a little better

[rbrhodes]

Hi Mike,

Here's a freebie shot at it. I must admit I get caught up in the problem so here you are, Too many things changed to list right now as its late and I spent way too much time on it...


When the process starts it shows the background data that is used to populate the form. Can I hide this from the customer?

=done. Master Wb opens to a blank sheet with data sheet xlveryhidden, accessible by password only , see also below



When I start the process the question seems to repeat the first question but puts answer into the second question, from there it is always one step out.


= Corrected. Also put in code to capture changes when going to/from Previous criteria/Next criteria. Your code wasn't capturing that although it was allowed...


As the form scrolls through the form I want the bottom three columns to show the URL of the relevant documentation for each section.


= Put in some fake ids on sheet3 that are loaded onto the form as each record is passed through


When I save the form away it saves the scripting as well so that the next time it opens it opens with the form re-enabled as though it is going to start a new record. I need it to save in such a manner that it is a plain form when re-opened


=WB now checks it's own name and opens differently depending on whether it's MASTER CRITERIA_beta3_dr.xls or whatever you call it and put in the code. Master opens to form with 'Admin button, all others open to data sheet ( sheet3)


= bunch of other stuff....






Admin and sheet 1 paassword is "bob", BTW.

[Mike_H]

Lucas.

First run and all looks fine. There was an error when saving but I think it was down to errmsg not being declared. (I feel chuffed I spotted that ) I added a dim line and it appears to be ok now. Thanks for all the assistance, it looks great. I will do some more tests over the weekend and let you klnow Monday if all the issues are resolved. Thank you again

Mike

[Mike_H]

rbrhodes. I am mightily embarrassed for calling you Lucas. Sorry old and grey and mind permanently in neutral these days. As I said before all looking neat and great. Have had a bit more of a play and canot see anything that looks out of place. I am really appreciative of your tie and effort

Mike

[rbrhodes]

Right. I didn't test the saving as the form was too big for my screen!