Suggestions please.....

[blazonQC]

First off, thanks for viewing my post! As a forewarning.... There is a lot of pre-info that you may find unnecessary, so I labeled all the different parts of the post for simple navigation. :)

I just can't seem to focus and I'm having issues with my 'problem solving' skills today, so I could use a few suggestions to help me along. Here's the situation:

Pre Info:

I am a QC at a steel mill and my boss recently discovered that I have a relatively broad background in computers and he decided to make me the unofficial computer tech/programmer/network administrator. Anyway, he gave me a project that I need to get done by Monday and i've hit a roadblock.

The Simple Project:

A spreadsheet in the style of a printable request form to confirm changes to 'Shop Orders' issued to all mill operators. The information on the spreadsheet, once completed, must be verified and signed by supervisors dependant upon which changes must be made to the orders. i.e "Order Quantity Change - Production Supervisor Signoff".

The Advanced Project:

With the basic project completed I am attempting eliminate all of the actual paperwork involved with this. I would like to have electronic signature accounts set up for all the supervisors for the form on the network, so the user can only change/verify specific information according to their position ("Production, Sales, etc") . I would also like our department to be able to view see a list of pending request applications so we can make sure that everyone is verifying information as quickly as possible to expedite the process.

Completed So Far:

1) I have databases set up to store all information submitted through the request form.
2) A 'master' userform layed out just like the request spreadsheet.
3) eSignatures created for all users

The Underlying Problem:

I have everything basically set up for user input..... but the issue is with the method of setting/retrieving the information back and forth between the 'App' and the spreadsheets. Currently I am coding everything individually and can't for the life of me figure out how to simplify the situation. I have an array declared as a custom Type which stores all information for each change set up like so:


Type tree
active as boolean
cel as string
team as string
desc as string
end type

Public vArray(0 to 19) as tree


And i'm using this array type to store the information and pass it to and from the db/sheet. When I hit the 'submit' button I have a procedure that fills all of the information manually into the array, meaning that I have to type all of the cell values and 'link' all of the information between the text/combo boxes and the array by hand. There has got to be an easier way to do this? At this point I have realised that if I continue with this method my app is going to run slow as hell and i'm going to end up typing a lot of 'If Then' statements.

In Conclusion:

I am just looking for a few suggestions on how to perform these operations more efficiently. Any input is valued! :)

Thank you for your time and sorry for such a long and detailed post. I can give more information if need be.

Chad

[Bob Phillips]

Bypass the UDT, move all of the data directly from the userform to the d/b sheet.

[blazonQC]

thanks for the reply :D . I've already thought about sending data directly from the form to the spreadsheet, but it's still going to leave me with coding a write function into each change event, which is still tedious and inefficient. And the type allows me to retrieve data from the db with 'active/disabled' status so I can display changes to be signed off on the form appropriately.

[Norie]

Why would you need a write function for each change event?

Why not just put the data on the worksheet when the user hits a button?

For example the submit button you already have.

[blazonQC]

I just misunderstood what xld was suggesting. I thought x was saying that as information is actively added to the userform, meaning that as it is typed into the txtbox it is transmitted to the sheet. The problem is that even with using the submit button to send it to the sheet, I would still have to 'link' the text boxes individually with their corresponding cells in the request form AND the db. Each value would have to be assigned their destination cell values, hence the use of the UDT.

Anyway, it's ok. I ended up figuring out how I would take care of the situation. I will ONLY write to the db until it is time to print out a finalized version of the request form. For the request print I have a sub that 'initializes' the array and fills it with cell values before the form loads. It just about all cleared up.

Thanks for the suggestions. :)

Chad

[Norie]

Chad

Why do you think that the textboxes need to be linked to the corresponding cells?:bug:

[blazonQC]

Norie,

I have to 'link' them because of the template my boss created. I'm on my comp at home atm. I'll install office real quick so I can seperate the template and post it for you so you can see what i mean. Check back in a little while.

Thanks
Chad

[Norie]

Chad

Sorry but I still don't see why you need to link the textboxes to cells.

You can easily write code like this to transfer data from a userform to a worksheet.

Range("A1").Value = Textbox1.Value

[blazonQC]

Norie

The code you give is what I consider 'linking' the textboxes to cells. The problem is that by doing that I will have to assign everything individually, and that it what i am looking to simplify:

From my original post:


Currently I am coding everything individually and can't for the life of me figure out how to simplify the situation.

I was just wondering if there was a better method I could use than that, because in the end i'm going to have to do a LOT of typing. With all of the userform information going into the Request Form and 2 seperate sheet db's , it's going to be a pain in the ass. I just figured there was some "magic" .Net or Excel function that would do some/most of it for me, since my old school coding habits (pre .NET) tend to get criticized by ppl that say "Why don't you just use the Application.Excel.Do.It.All.For.You function?" :P . Please pardon my sarcasm. I think i have come up with a relatively simple solution for this problem and I will post it here when I finish the project.


Chad

[Norie]

Chad

The code I posted doesn't link the textboxes to the worksheet, or vice versa.

All it does is place the values in a cell.

If that's not what you want then I think you need to explain further.

[lucas]

The question on the tip of Nories tongue is...do you want your data to be put in specific cells or in the next blank row....not overwriting previous data...?? I don't really understand what your trying to do either obviously.


If someone has a copy of this function that they aren't sharing, I would appreciate a copy of it:
Application.Excel.Do.It.All.For.You function

[blazonQC]

If someone has a copy of this function that they aren't sharing, I would appreciate a copy of it:
Application.Excel.Do.It.All.For.You function

lol. I hope you realise I was being sarcastic. :P . Anyway here is the 'template' I set up attached to the post. This is ONLY the request form that will be used only when a request has been signed off and should be printed. So no information will be stored here. My question was just an inquiry to a more efficient method than Norie's method.

range("A1").value = textbox1.value

This is the solution I have come up with which, imo, is more effective in terms of coding than writing everything in individually, i.e

range("A1").value = textbox1.value
range("A3").value = textbox2.value
range("A5").value = textbox3.value
etc...

My solution:

Set up a control array and declare a variable array as a custom type

type tree
active as boolean
cel as string ' < there is a reason for this data type
team as string
desc as string
end type

Dim vArray(0 to 19) as tree


Then initialize the array when a new instance of the form pops up to pre load it with the Ranges:

Public Sub InitArray()
Dim tmp As Integer
tmp = 8
For i = 0 To UBound(vArray)
vArray(i).active = False
vArray(i).desc = ""
vArray(i).team = ""
vArray(i).cel = "B" & CStr(tmp)
tmp = tmp + 2
Next i

End Sub

Public Sub XLWrite()
For i = 0 To UBound(vArray)
If vArray(i).active Then
Worksheets(1).range(vArray(i).cel) = vArray(i).desc
End If
Next i

End Sub


From here you can use the control array to fill in the rest of the blanks in the 'vArray' which will hold all of the information we need to write/retrieve information to/from our db. These aren't examples from my code specifically, just doing it off the top of my head. but this code would work with the sheet I have posted here. Now i'm lost in what i was saying. :P

Chad

btw Norie,

I think you are just confused as to what I mean by linking the cells to textboxes:

Range("A1").value '<-- 1st memory address containing a value
= '<-- operator that 'links' the value at the 1st memory address with the 2nd
textbox1.value '<-- 2nd memory address

[lucas]

Hi Chad,
Help me understand ok? You have a template that you wish to populate repeatedly with info from a database and print each one...if that is correct...do you wish to keep a copy of each template in the excel file?

[blazonQC]

Lucas,

Well, the template I posted is just 1 part of the workbook. You are correct that I want to use that template for printing, but that's all it will be used for. The databases will be the only place that the actual data to fill in the template will be stored. I figured this would be a better method to go with than saving copy after copy of the template. The workbook includes that template sheet, and 2 database sheets, both hidden. One db is for pending request(unsigned) , and the second db is for the permanent data where all of the verified/signed requests will be stored for future reference. lol. I'm sorry if this is all getting confusing, I'm actually confusing myself here. I'll try to draw out a simple diagram for it.

Chad

[johnske]

Post the workbook would be easier :)

[lucas]

Attached is an example of printing to a template from a data sheet...you have to check the ones you want printed and its set up for preview instead of print....from one of Ken Puls files that I adapted...see if it helps with the printing part..

John's right...it doesn't help to hold back info from us. You can clean up any propriotery info before posting but we can help with the problem easier if we can see what your working on....

[blazonQC]

lol, not trying to hold back info, but everything is broken into peices atm and is coded for a network drive. I have to get this done tomorrow so i'll be working on it hardcore all day. I've come up with a solution that I think will work best for me and when I get done I'll post the whole workbook up for you guys to review. :D . Check back tomorrow for updates!

[Norie]

The = operator does not 'link' anything as far as I'm aware.:bug: