[nynamyna]

Hi everybody

I am new to this forum.....I have a very basic knowledge of VBA .....So please bare with me.....

I am designing a tool to effectively schedule the surgeries...

I have an excel file in which if I click 'click here to schedule a surgery' button then a userform will open ....In that userform date will be chosen and the number of nurses required for a surgery is entered......I have another excel file in which the maximum number of nurses for a day is already saved (Input file for # of nurses.xls)....What I need is ....for eg: If the maximum number of nurses available for a day is only 10 then if I try to schedule more than 10 nurses for a particular day(for eg: one surgery may require 5 nurses, another may require 4 and the third may require 3 at a same time but different operating rooms..) then a message box should open saying 'there is no enough # of nurses available on the chosen date'

I have attached a sample file along with the additional excel files .......

Thanks
Bharath

[OBP]

Bharath, welcome to the forum.
It would be a bit easier if the Input file for # of nurses.xls and Input file for patient details were other Worksheets in the same Workbook, rather than completely separate workbooks.
To assist you further we would need to know where you are going to store the # of Nurses and it would be much easier to calculate if you also have a cell containing the Total Number of Nurses aleady booked for a particular day.

Have you considered using Access for this application?

[nynamyna]

Hi OBP

Thanks for your reply.......Originally the main excel file itself has 365 sheets so they are asking for separate workbooks in order to easily access it and print it out......As now the # of nurses for each day is saved in the excel file called 'Input file for patient details.xls' under a separate column called '# of nurses'......Those people need the application only in excel so access cannot be used.......In the excel file attached (Input file for # of nurses.xls) I have already scheduled the total number of nurses for january......I am attaching it again

Thank you very much for your help

Bharath

[OBP]

So your form is going to have to Identify the correct Worksheet for the Date Selected?
Each Sheet will need the total number of nurses already booked?

[nynamyna]

what you say is right........My userform already identifies the correct work sheet according to the date chosen and saves the data entered in an excel file called ' Input file for patient details' .......similarly it should identify the correct worksheet for a specific date chosen retrieve the total number of nurses from it.......Yes each sheet will need the total number of nurses already booked for a particulaar date....If the total number of nurses is not entered in the excel then it should say 'Nurses are not scheduled so please schedule them first'

[OBP]

Bharath, if your knowledge of VBA is very basic, how have you produced the code that you have already, as it is pretty good?

[nynamyna]

That was a good question........I asked a person's help.....He used to tell me everything.....Right now he is busy so I couldn't ask his help......tats why I am seeking help.......Moreover the time given is also very less....I need to show some improvements each week ..Tats y I ask help online rather than studying from the basic.....Now I am also learning VBA from basic and also showing some improvements tats y I chose this way......

[OBP]

OK, I am not so quick with Excel VBA as I hardly ever use it, as I work mostly in Access. So I will have to post something tomorrow.
Maybe Zack Barresse will come along and do it much quicker and more proffessionally.

[nynamyna]

HI OBP

Thank you very much for your help..... I will wait till tomorrow....No problem for me.......Should I contact Zack Barresse or he himself will reply to this thread.......

[OBP]

If he is available he might see it and respond. You could try asking him to take a look.
If not I will post something tomorrow.

[OBP]

I have the first part., select a day & month & then enter a value in the # Nurses field.
Enter day 13 and August.

[nynamyna]

Hi Anthony

Thanks for the immediate response........so what you sent me retrieves the total number of nurses on the scheduled date and says that through a message box........

I have an other doubt in the following macro

'
' Macro2 Macro
' Macro recorded 12/08/2009 by Anthony Osborn
Dim monthname As String, wbInput As Workbook, wsInput As Worksheet, wbdata As Workbook
Set wbdata = ThisWorkbook
Set wbInput = Workbooks.Open(ThisWorkbook.Path & "\" & cstrFILE_INPUT)
Set wsInput = wbInput.Sheets(ComboBox1.Text)
Range("I1:O3").Select
With Selection
.MergeCells = False
End With
monthname = Format(ActiveCell.Value, "mmmm")
MsgBox monthname
With Selection
.MergeCells = True
End With
nonurses = Cells(1, 1)
MsgBox nonurses & " " & monthname
Exit Sub
'
End Sub

If I choose a date and a month for which the total # of nurses is already scheduled......... then it retrieves the total number of nurses and pops up a window with the total number of nurses..........But If i have not entered anything for the total number of nurses for a month in the ' input for the # of nurses' excel sheet then according to the above macro....... if there is no nurses scheduled for a month then a message box should open saying no nurses and month name ........but it is not happening ...

If I run the above macro seperately it gives run time error I don't know why.....I made sure that all the files are in the same directory but also it gives a 'run time error 1004'......


Thanks
Bharath

[OBP]

Bharath, sorry, that was just me playing around looking at the data on the Worksheet rather than the user form, so you can delete that.

[OBP]

We now need to look at the number of Nurses already booked for the chosen period(s) and compare that to the number available.
But that will probably have to be for tomorrow.

[nynamyna]

I just confirmed it tats all....so as of now the code retrieves the total number of nurses and pops up a window with the total number of nurses right.....I ll chat with you tmro ....thanks

Thanks
Bharath