bet noboby knows!...lol
Posted by andy evans on November 13, 2001 5:44 AM
Ok, heres what I need
I have a list of about 30 items of hospital equipment for loan. They are on a spreadsheet and can either be on loan or in stock (out or in).
For example there are 5 x junior wheelchair jw1-jw5
5x medium wheelchair mw1-mw5 and 7 x adult wheelchair aw1-aw7
I want to use a user form with a combo box so that when someone chooses say junior wheelchair from the list it looks at the right list jw1-jw5 and then looks in the column next to each one until it finds an entry that is 'in'. At that point i want to change the entry to out and use input boxes to add patient name address etc (I pretty much know how to do that stuff)
The bit I cant crack is getting vb to find a range then go through each antry and the one next to it until it finds the required answer then sets that cell as active.
Can it be done?
Its a project that has been set by an exam board for my students and I don't have to solve it this way but it would be nice.
thanks in advance
Posted by Juan Pablo on November 13, 2001 6:00 AM
How are your columns organized ? Can't see the pattern from the example. It's not that difficult, but you could explain some more.
Juan Pablo
Posted by andy evans on November 13, 2001 6:28 AM
columns arranged as follows but can be shifted around
Code Item Availability Total
JW1 Junior Wheelchair IN 1
JW2 Junior Wheelchair IN 1
JW3 Junior Wheelchair IN 1
JW4 Junior Wheelchair OUT 0
JW5 Junior Wheelchair OUT 0
MW1 Medium Wheelchair OUT 0
MW2 Medium Wheelchair IN 1
MW3 Medium Wheelchair IN 1
MW4 Medium Wheelchair OUT 0
MW5 Medium Wheelchair OUT 0
AW1 Adult Wheelchair IN 1
AW2 Adult Wheelchair IN 1
AW3 Adult Wheelchair OUT 0
AW4 Adult Wheelchair IN 1
AW5 Adult Wheelchair OUT 0
AW6 Adult Wheelchair OUT 0
AW7 Adult Wheelchair IN 1
The numbers are so that you can get a report of how many are in stock.
Is that any further help?
Posted by Rick E on November 13, 2001 6:30 AM
Re: Hey, let me paint that fense...
Here is what you want, first define names for each of the different items that contain the status of them. So the column next to jw1 - jw5 is given the name jwStatus, mwStatus, awStatus, ect. then here is the code (macro: findIn) to update the "in" to an "out" and capture the row and column of what was updated:
Sub findIn()
Sheets("Sheet1").Select
' logic to select the name here
str1 = "jwStatus" ' this example it was junior
Range(str1).Select ' this selects the range
ck = 0 ' switch checks that there is an 'in'
For Each Item In Selection
If Item.Value = "in" Then
Item.Value = "out"
theRow = Item.Row
theColumn = Item.Column
ck = 1
Exit For
End If
Next
If ck = 0 Then
MsgBox "No item avaliable."
Exit Sub
End If
' you now have the row and column of the item that was updated
End Sub
Hope you know how to define "names" in Excel
Posted by Juan Pablo on November 13, 2001 6:32 AM
So, if i choose "Junior Wheelchair", i would change "JW1" ?, in "Medium Wheelchair" you would change "MW2" ? and in "Adult Wheelchair" "AW1" ?
Juan Pablo
Posted by andy evans on November 13, 2001 7:20 AM
Yes if you chose say junior wheelchair, it would look at the list see that JW1 ia avaialable, change it from in to out then start the input forms.
If you chose medium wheelchair from the combobox it would find that mw1 is out so would look at the next one in the list, find that that is available and change it to out, then start the input forms etc
Posted by Juan Pablo on November 13, 2001 7:50 AM
Did you read Rick E's post ? if didn't work then reply and i'll work on it.
Juan Pablo Yes if you chose say junior wheelchair, it would look at the list see that JW1 ia avaialable, change it from in to out then start the input forms. If you chose medium wheelchair from the combobox it would find that mw1 is out so would look at the next one in the list, find that that is available and change it to out, then start the input forms etc
Posted by andy evans on November 13, 2001 11:47 AM
Re: Hey, let me paint that fense...
Dear Rick,
Thankyou for your help, this site and people like you restore faith in mankind.
I do know how to define names, so thats not a problem, but I have not had time to work through your code yet. I will try it out later.
Many thanks (now lets see if it works...lol)
Andy
Posted by andy evans on November 13, 2001 11:55 AM
it sort of works!
Well I tried that code Rick, I can't say I understand it yet, but will keep trying.
It certainly swaps the 'in' for 'out' but it finishes up with the column selected and no indiacation of which jw item was changed - i want the active cell to be the one for the jw item just changed.
Also does this mean I have to store each set of items on seperate sheets? Not a problem if i do.
Posted by andy evans on November 13, 2001 11:57 AM
it sort of works! part 2
What does 'ck' mean in the code? can't find it listed in help.
Posted by Rick E on November 14, 2001 11:46 AM
Re: Define Names
TO define a Name - select the data you want named, in this case it is the "in" and "out" stuff for an item. (e.i. C10:C15) then on the menu bar select "Insert" then "Name" then "Define" type in the 'name' for this selection, (i.e. jwStatus) do this for each 'name' you need. My code just did the hard part as I recall, but you still need the OTHER stuff, can not write this code for you at this time. Sorry Rick E. Dear Rick, Thankyou for your help, this site and people like you restore faith in mankind.
Posted by Rick E on November 14, 2001 11:51 AM
Re: ck is a variable I defined (used) in the macro.
ck is a variable I defined (used) in the macro.
Since I did not keep the code, I am not sure what I used it for, but it usually "check" for some condition and if found does something like send a message (Data not found!) for example. What does 'ck' mean in the code? can't find it listed in help.
Posted by Rick E on November 14, 2001 11:56 AM
Re: Answers...
You can have many Excel "names" defined on a sheet and in a workbook, as I recall, I left the row and column values for you to use to select which items was updated. I did not kept the code!!! Sorry. Well I tried that code Rick, I can't say I understand it yet, but will keep trying.
