I am stuck. HELP!

mhemat

New Member
Joined
Apr 14, 2011
Messages
30
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try this: Press ALT + F11 to open the Visual Basic Editor. In the Project window double click ThisWorkbook then paste this into the white space on the right

Code:
Private Sub Workbook_Open()
With Sheets("Identification").Range("C3")
    .Value = .Value + 1
End With
End Sub

Press ALT + Q to close the code window, save the workbook. Close and re=open it to see the effect.
 
Upvote 0
Thank you, the code works. I can save the file under the new ID number, but when I open the blank template for new patient, c3 shows me the ID number that i just saved it for the preious patint. Do I make sense? Let me explain it: The first ID number was "1". I entered the new paitent's info under that number and saved it as 1 jane dow. for a new patient I go to the blank template and still gives me ID number "1". Huge thanks from the bottom of my heart.
 
Upvote 0
No. I mean C3 still gives me ID "1" after I saved that ID number (1) for the previous patient. I want c3 on the template gives me the next ID number (in this case "2") for a new patient.
 
Upvote 0
The value needs to be entirely numeric for my solution to work. Give an example of an anonymous patient ID.
 
Upvote 0
1006353 is the last number assigned to the previous pt. I saved that file as 1006353 and stored it in the folder named Participants. When I open the blank template for a new patient, I want c3 gives me the next available ID number (in thnis case 1006354). After inputing the information of the new pt. on the blank template, I will save it in the Participants folder as 1006354. So, for the next pt. when I open the blank template it should give me 1006355 in cell 3. Am I clear? I am sorry for taking your time.
 
Upvote 0
Then I do not understand why it is not working. Sorry. Macros must be enabled.
 
Upvote 0
it worked. I must save the blank template with the new ID number first,using the Save button or Ctl-S, then I input the ifon and save it as. My next question is there will be several users to use this blank template for new patients. Is there a way that as soon as the blank template is open with the next ID number, a dialog box to be open alerting them that they need to save the template first before they can input info? Thanks
 
Upvote 0
Maybe

Code:
Private Sub Workbook_Open()
With Sheets("Identification").Range("C3")
    .Value = .Value + 1
End With
MsgBox "Please save this file before entering data", vbInformation
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,609
Messages
6,179,874
Members
452,949
Latest member
Dupuhini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top