I am stuck. HELP!

[mhemat]

I am a addiction counselor with a basic knowlege of excell working in a non-profit residential addiction recovery center. Our case mangement software is bugged and not working. So, I created a spreadsheet to at least shows what beds are occupied and which are empty to bring in new patients. Now, my boss thinks that I am a hero and can create a new case managemnt program. I created a 8 tabs (sheets) template on excell and it looks good. Some cells are merged to have enough space. I also created the 9th sheet to link some opitions to some of the cells by linking them to those cells. Such a race and drug of use. Now my question is:
I want be able to everytime I open the template, either a new unique ID number shows uo in a cell C3, sheet's name (Identification) or to click a icon to create a new ID number in that cell.
2- How can I export the information from this workbook (Template) to a database (Excell or access) so, I can get reports of all current or prior residents.
I know I am asking too much, but our agency does not have the resources to hire a counsultant or buy a new case management program. Thank you

 

[mhemat]

This code worked great. Thank you. I was just wondering if there can be a Save button on that Msg box along with Cancel, instead of OK that is already on it as a result of your code? Am I asking too much? Pardon me.

 

[VoG]

Try

Private Sub Workbook_Open()
With Sheets("Identification").Range("C3")
    .Value = .Value + 1
End With
If MsgBox("Please save this file before entering data", vbInformation, vbYesNo) = vbYes Then Me.Save
End Sub

 

[mhemat]

No, it still is the same as the previous code. It only states that the file needs to be saved before entering data. I have to maually save the file. I was interested on having the Save & Cancel buttons on that Msg box. Thank you very much VoG.

 

[mhemat]

The following code is for:
1- Auto numbering
2- To askes "Continue or not"
3- To save the workbook automatically if answered Yes to continue, otherwise to close the workbook.
My questions are:
1- password for the sheet protection is (tfc), no parenthesis. how can i insert my password in the following code?
2- How can I use this formula ONLY for the workbook named (New Participants), no parenthesis. Thank you all

Private Sub Workbook_Open()
Application.DisplayAlerts = False
Dim S As String
If Not MsgBox(" Is this a new participant? ", vbYesNo + vbQuestion, " Administration Asks....") = vbYes Then
bolOpening = True
ThisWorkbook.Close False
End If
Sheets("Identification").Select
ActiveSheet.Unprotect ("Password1")
Sheets("Identification").Range("C3").Value = Sheets("Identification").Range("C3").Value + 1
ActiveSheet.Protect ("Password1")
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub
<!-- / message -->

 

[Akashwani]

Hi,

Just change Password1 to your password, "Password1" = "tfc"

Sorry, I don't understand Question 2, you seem to know where to add the code VoG supplied, so delete VoG's code and add the new one.
To prevent the code being added to the new workbook that is created from the Template I use this code...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False

If Not bolOpening Then

Worksheets("Identification").Copy
ActiveWorkbook.SaveAS "C:\Documents and Settings\Ak\Desktop\Test Folder\" _
     & " " & Range("C3").Value _
     & " " & Range("B7").Value _
     & " " & Format(Now, " dddd d mmmm yyyy   hh-mm-ss AM/PM") & ".xls"

ActiveWorkbook.Close True

Application.DisplayAlerts = True

The File is saved with the value in C3 (ID number), Plus the value/text in B7 then the date and time. So I would have a file saved in Test Folder on my Desktop with this file name for example.......

2233445 TEST Saturday 16 April 2011 08-10-21 AM

The next file saved would be something like this....

2233446 AnotherTEST Saturday 16 April 2011 08-15-35 AM


Ak

 

[mhemat]

Thanks for responding. let me explaine what I am trying to do. I have auto numbering code in my template. I also have a code that automatically askes the end user to save the file first, before entering data. This helps the auto numbering to generate a new number each time the teplate openes up. When the end user enter new patient's data then, he will save it under ID number in C3 (Hence, auto numbering). Each patient will have a unique ID number. My question was that so far so good, but the code moves with the newly saved file and as soon as a user opes up the previously saved file to edit, the code will ask them to save and assignes them a new IDnumber. So, How can I be able to do all things as I mentioned before and at the same time prevent the code to travel with the saved as files. The Saved As files are a regular files with the patient's data that mostly is used for updating. Thanks again

 

[Akashwani]

Hi Mhemat,

I use the following code for my template...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False


'''Save code
Worksheets("Identification").Copy
ActiveWorkbook.SaveAS "C:\Documents and Settings\Ak\Desktop\Test Folder\" _
     & " " & Range("C3").Value _
     & " " & Range("B7").Value _
     & " " & Format(Now, " dddd d mmmm yyyy   hh-mm-ss AM/PM") & ".xls"
ActiveWorkbook.Close True

''''Delete data
    Range("B7:B15,D9,A12:E38,E41,E42").Select
    Range("E42").Activate
    Selection.ClearContents
    Range("A7").Select

ActiveWorkbook.Save


Application.DisplayAlerts = True

End Sub

When the user has entered the data they click the X button to close. The worksheet is copied and saved to the location specified. The data entered in the Template is then deleted and the Template is then saved with no data except for the ID number.

I hope this helps you resolve your problem.

Ak

 

[mhemat]

AK, you sre a God send. My workbook has 8 sheets and each sheet has cells designed for input. Your code worked great, but how can i assign more than one sheet. Lets say that sheet one is named identification with cells to be deleted d4, d7, e4 and sheet 2 is called intake with cells c3,c5 to be deleted. Also would you please explain what this part of your code does:
Range("B7:B15,D9,A12:E38,E41,E42").Select
Range("E42").Activate
Selection.ClearContents
Range("A7").Select

 

[Akashwani]

Hi Mhemet,

I'm sorry, I don't know how you would apply the code I gave you to more than 1 sheet, you need a real expert to help you with that, perhaps you should start a new post asking for someone to amend it for you.

Range("B7:B15,D9,A12:E38,E41,E42").Select
Range("E42").Activate
This part of the code selects the cells that require deleting/clearing

Selection.ClearContents
This clears the contents from the above cells that have been selected.

Range("A7").Select
This puts the cursor onto A7 ready for data to be entered.

On Sheet Identification you would probably use this....

Range("D4,D9,D7,E4").Select
Range("E4").Activate
Selection.ClearContents
Range("D4").Select

I hope this helps you to resolve your problem.

Good luck