PDA

View Full Version : I am in way over my head Need help



vicC
02-10-2010, 04:44 PM
I am an nurse with some knowage of excel, but I love working with this Stuff.
I think that using an userform might be what I need.

I have tried to upload my file but it is to big.

I need help adding and editing:banghead: an table that I made in excel 2007.
table has index column 1, date column 2

It is for staffing meetings that happen two times an day.
need to be able to Add and edit, by date .

field names

PCU census PCU staff
ICU census ICU staff
Date

Any Ideas would be great!
Thanks.

Aussiebear
02-10-2010, 05:03 PM
Just post a sample workbook of your workbook so we can see what you are working on. Failing that, have you tried compressing your file?

vicC
02-11-2010, 07:24 AM
:hi:OK I have some code here and some examples
of worksheets.

The interface sheet is what I think I need to make into user forms.

I tried to explain in workbook ask anything you need.
thanks again.

Bob Phillips
02-11-2010, 08:03 AM
Oh come on, that does even start to tell us what you need.

Talk us through what you do/need to do to get the information entered and updated correctly. We haven't a hope of second-guessing our thoughts on that huge table.

vicC
02-11-2010, 02:46 PM
//sites.google.com/site/myhelpsiteproject/files

This is a copy of the working file.

This file helps keep track of staffing variances. Too few nurses for the number of patients.

This is the interface.
There are to staffing meetings everyday one in the morning and one at night. The number of patients is added. " The census" than the number of staff for each unit. After that if the difference is a negative number we have to enter an action of how we corrected it.

I need opinions: is the interface the best way or to use user form
if user form is the best way, I have no idea how to make it work. Second opinion : is the "DBtable" ok to hold data?
Opinion 3: the way I copied data into DBtable ok?
I am using 2007 but need it to work also in 2003 which I know is not working very well.

The staffmatrix page is cut paste page. Copy of matrix from our Staffing program.

Sup census just page to get total census for shift to cut and paste into different report that they type up every shift.

Also need reports to print out the % of variances for a month, year, and quarter. That include date of variance and unit.

I know I have not given you what you need to help me. But maybe after you see what I have made you can ask questions to help me?

Thanks:dunno

Bob Phillips
02-11-2010, 02:57 PM
It is hard to say as you are describing what we see, not what you want to do.

I looked at what you made and after scrolling right for hours on end, I gave up looking. Words help me more than guessing your intentions.

However, I would venture that you don't need a form. Just enter the data on the staffing sheet. You can then either build a report using formulae, or else use VBA to build it.

Aussiebear
02-11-2010, 03:40 PM
Vic, This is more like a project rather than a single issue. Having said that why not break it down into a series of issues. Firstly, write down what you think the main issue is.

If as I'm guessing you are questioning the layout then you need to consider, who uses the workbook and how many of the sheets they need to be able to access and more imortantly whether this project might be more suited to a true database rather than Excel. When you are clear in your mind as to what you want, post back to here.

At this stage I'm in full agreement with XLD. The current layout is confusing since we don't yet follow what your doing.

vicC
02-11-2010, 03:58 PM
You are right I have no Idea. I am making list.

It is very hard to get out!


thank you for you time, I am getting my pen out.

will post back.:think:

Aussiebear
02-11-2010, 04:07 PM
We look forward to your next post

Aussiebear
02-11-2010, 06:31 PM
A couple of other things for your consideration, while you are thinking about what it is that you are chasing. I've been looking at your code and I'm somewhat confused. Can you tell me what the following code is meant to be doing?


Sub copyintodatabase()

Application.ScreenUpdating = False

Sheets("staffDB").Select
Application.ScreenUpdating = False
Range("C1").Select
Application.ScreenUpdating = False
Dim vicday As Variant

Application.ScreenUpdating = False

vicday = Application.Match(Range("C1"), Range("A11:A2910"), 0) 'finding index date
Dim vicrow As Variant
vicrow = vicday + 10
Worksheets("staffDB").Range("C4:J4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date

Application.ScreenUpdating = False
'PCU DATA
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "C"), Cells(.Row, "J")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "DT"), Cells(.Row, "EA")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'SURGICAL DATA
Worksheets("staffDB").Range("K4:R4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "N"), Cells(.Row, "U")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "EE"), Cells(.Row, "EL")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'REHAB DATA
Worksheets("staffDB").Range("S4:Z4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "Y"), Cells(.Row, "AF")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "EP"), Cells(.Row, "EW")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'MEDICAL DATA
Worksheets("staffDB").Range("AA4:AH4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "AJ"), Cells(.Row, "AQ")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "FA"), Cells(.Row, "FH")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'CVCU DATA
Worksheets("staffDB").Range("AI4:AP4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "AU"), Cells(.Row, "BB")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "FL"), Cells(.Row, "FS")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'ICU DATA
Worksheets("staffDB").Range("AQ4:AX4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "BF"), Cells(.Row, "BM")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "FW"), Cells(.Row, "GD")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'MCU DATA
Worksheets("staffDB").Range("AY4:BF4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "BQ"), Cells(.Row, "BX")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "GH"), Cells(.Row, "GO")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'PEDI DATA
Worksheets("staffDB").Range("BG4:BN4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "CB"), Cells(.Row, "CI")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "GS"), Cells(.Row, "GZ")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'L&D DATA
Worksheets("staffDB").Range("BO4:BV4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "CM"), Cells(.Row, "CT")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "HD"), Cells(.Row, "HK")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'NURSERY DATA
Worksheets("staffDB").Range("BW4:CD4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "CX"), Cells(.Row, "DE")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "HO"), Cells(.Row, "HV")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'CDU DATA
Worksheets("staffDB").Range("CE4:CL4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "DI"), Cells(.Row, "DP")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "HZ"), Cells(.Row, "IG")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If

Application.ScreenUpdating = False

Sheets("interface").Select
Range("D8:D18").Select
Selection.ClearContents
Range("F8:F18").Select
Selection.ClearContents
'Range("H8:H18").Select
'Selection.ClearContents
'Range("I8:I18").Select
'Selection.ClearContents
Range("K8:N18").Select
Selection.ClearContents

Range("A1").Select
Application.ScreenUpdating = True
Application.Save
'Application.Quit

End Sub

My first question is does this code actually work? If it does then great but, I would suggest that it can be improved in a number of ways.

Firstly there are a number lines of code that can be removed or rewritten to shorten the total line count.

For example: In the first 8 lines of code you've got 4 lines of "Application.ScreenUpdating = False". Normally you only require one to turn off Screen Updating and one to turn it on again at the end of the sub as in the following example

Sub CopyIntoDatabase()
Application.ScreenUpdating = False
'Relevant code here
Application.ScreenUpdating = True
End Sub

The 2nd example I'd like to bring to your attention is, Declaring the Variables. I had to look through your code to find the Dim statements when I was expecting to find them at the start of the Code. Its a good habit to get into, as well as using the term Option Explicit at the top of your code. Option explicit forces the user to declare all variables and as a result cuts down on any errors resulting from misuse of variables, particularily typo's.

The 3rd example for you to consider is as follows, your 3rd line of code is
Sheets("staffDB").Select Selecting cells, ranges, sheets etc slows down code execution dramaticily, you could try rewriting it as such
With Sheets("staffDb")
And a 4th example to consider is, where lines of code say
Sheets("interface").Select
Range("D8:D18").Select
Selection.ClearContents
Range("F8:F18").Select
Selection.ClearContents
this can be easily shortened into the following

With Sheets("interface")
Range("D8:D18, F8:F18").ClearContents
End With

With that in mind I'm going to have a crack at tidying up some of the code for you

Bob Phillips
02-12-2010, 03:06 AM
With Sheets("interface")
Range("D8:D18, F8:F18").ClearContents
End With




With Sheets("interface")
.Range("D8:D18, F8:F18").ClearContents
End With

Aussiebear
02-12-2010, 03:12 AM
THis workbook knows how to tie up resources. The more I look at it, the more I'm convinced that using excel alone is not going to be of any use. The formatting alone is massive weightage for the workbook. The code can be tidied up, but then it needs to be useable, and this is where we come unstuck.

Vic, if you are still dead keen on this, then you seriously need to sit down with professional programmers. There are some people here who may be able to assist you for a fee. Best of luck if you wish to continue

Aussiebear
02-12-2010, 03:13 AM
With Sheets("interface")
.Range("D8:D18, F8:F18").ClearContents
End With


very good point Bob, if you'll pardon my pun

OBP
02-12-2010, 07:18 AM
Looking at this Workbook and it's application I think it would be better in Access, especially if it is to be shared by other users.

Bob Phillips
02-12-2010, 08:40 AM
Whislt I am struggling to see what he wants to do, I do get a sense of what the data is for, and Access seems a terrible option to me, far better to leave it in Excel.

OBP
02-12-2010, 09:53 AM
XLD, not when I see things like this
"The staffmatrix page is cut paste page. Copy of matrix from our Staffing program.

Sup census just page to get total census for shift to cut and paste into different report that they type up every shift."

vicC
02-25-2010, 11:19 AM
Thanks you guys for all the imput I am trying to clean up the code. and learn more. I do not think they will pay.Program just makes it easier for us.

vicC
02-25-2010, 11:40 AM
The code breaks down data and copies it into table under nursing unit ie: ICU, PCU. Also each unit has AM and PM meetings to save data from I know should have them beside each other. Just learning. I need the "interface to reset for lack of better word back to 0 for each meeting." This are very smart people that want to mess with everything, at the time I did not known how to make userform work in excel and have not made it work yet.

Would like to reinvent the wheel and make userform out of interface.

thanks for any input and everything you have said is taken very greatfully!

SamT
02-25-2010, 02:58 PM
Vic, you are a VlookUp King!

Next time, remember;

Input >> Database
Database + Static Tables >> Output Reports

And never mix Db or table with reports on the same sheet.

How urgent is this project?

SamT

SamT
02-25-2010, 03:00 PM
There is a chance, how good idunno, that this organic growth will work if all Merged Cells are converted to Horizontal Align = Center Across Selection.

vicC
02-25-2010, 05:39 PM
Not all that import to rush, just need to make this work and learn a lot. I am able to collect data with this as of right now, but making reports and making it work much more smooth would be nice. I started this thread to learn and refine, rebuild the thing. I can see the power vba has an want to learn much more of this really cool stuff!!! I am also taking classes for my nursing stuff the days are very short.

vicC
02-25-2010, 05:44 PM
The vlookup was my first true love, but is there an better way?

vicC
02-25-2010, 05:46 PM
matrix does change but not to often.

SamT
02-26-2010, 10:57 AM
Vic,

Attached is a new staffDB worksheet for use in your Workbook Remodel Project.

It is a database because the Defined Name staffDB!Database refers to the entire sheet usedas a database

The First Row is set as Field Names by Formatting it with a different Bold font than the Recordset and by a bottom border.

Each Column has also been made a Named Range with a Range Name identical to the Field Name. These Range Names are Worksheet specific.

All this means that you can use database functions as well as Range Methods without trying to remember which name refers to which.

I htink you can use Named Ranges in VLookUp.

If you remove those pesky unneeded drop down buttons From all the action columns, the file size should shrink some more.


SamT

vicC
02-26-2010, 12:04 PM
Is there an limit to the number of named ranges you can have?

SamT
02-26-2010, 02:52 PM
I don't really know.

There's Worksheet Specific Named Ranges prefixed when created by the worksheet name and an "!"

And Workbook Names, defined without the sheet name.

And Formula Names of both type, which refer to a Formula instead of a Range. =VlookUp(blah,blah,blah)

I thimk you can even create local name that refers to a range in another workbook. =['Otherbook.xls']SheetName!Range.

Speaking of formulas, I've attached a worksheet I made as an example in a tutorial I'm writing. I'm reccommending to my select students that they keep all formulas on one sheet and Name the results cell.

They won't be using near as many formulas as you did, since I'm going to have them use Pivottables for reports.

The reason I'm recommending this method is because they have cause to occasionally change formulas and this method makes it so much easier to understand and maintain them.

SamT

SamT
02-27-2010, 10:28 PM
bump

Bob Phillips
02-28-2010, 04:47 AM
bump

Why are you bumping it, are you aksing a question?

vicC
02-28-2010, 09:24 PM
Thanks I'll take an look!

vicC
03-01-2010, 01:57 PM
I am going to try again.
I have made an user form to collect data.
taken all pull downs out.

I have the user form coming up from button.

I need Error code if user does not select AM or PM

Also code to enter data into DBtable using date I guess to find record.

What I tried just laughed at me.:dunno

Need code to use Am or Pm selection to find right cells to place data

Take an look an see if you can help.

I will try to answer all questions.

Thanks.

Bob Phillips
03-01-2010, 02:04 PM
Why bother with the form. Why not enter the data straight on to the worksheet?

vicC
03-01-2010, 04:16 PM
Nursing supervisor does not what to have to think about it!
I also think it would be less change of error. I want to hide the DBtable from them so they do not mess with it. They will get to this on the public drive of an network.
and I also will learn how to use code for something else.

Bob Phillips
03-01-2010, 05:20 PM
You can put all sorts of checks on the data sheet, data validation, event checks and so on. You have so much richer functionality built-in to a spreadsheet than a form, and the layout can be so much richer, clearly indicating where to input the data. I am no fan of forms when you you can use a spreadsheet.

SamT
03-03-2010, 02:17 PM
Vic,

Thanks to XLD, here's a Staff Matrix Workbook you can use now.

There's an instruction Worksheet in it.

SamT