PDA

View Full Version : VBA Help - Desk sharing solution - thanks in advance



Icikle
09-26-2012, 06:35 AM
Hi,

Im trying to create a desk sharing database for where i work for hotdeskers to book available desks.

The info is broken down by person and whether theyre in or not by half hour slots from 8 am to 8pm. The slot shows as red if theyre in and green if theyre not in, signalling whether their desk is available.

What I want to do is have a drop down option for each cell so users can select a name to reserve a desk for. I have done that, and once they do it becomes orange instead of green. Now I have shortened the width of the cells so that the persons name doesnt appear as its just too wide with the amount of names that will eventually populat the list, but what I want to do is make it so that a text box appears with each cell that is selected. If the cell is green it shows as desk available, if the cell is red it shows desk unavailable and if reserved for a person it shows reserved for the persons name.

I have this code so far but have no idea if im on the right track and it doesnt seem to work at all:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With TextBox1
Select Case Target
Case "IN": .Visible = True: .Top = Target.Top
.Left = Target.Left + 60: .Text = "Desk Unavailable"
Case "OUT": .Visible = True: .Top = Target.Top
.Left = Target.Left + 60: .Text = "Desk Available"
Case Is <> "": .Visible = True: .Top = Target.Top
.Left = Target.Left + 60: .Text = "Reserved for " + selection.value
Case Else: TextBox1.Visible = False
End Select
End With
End Sub
The case <> " is the only thing i could think of to return a result if the cell is neither in nor not in and the selection.value, i have no idea if that part will even work but im trying to make it so it shows the name of the person populating the reserved slot.

any help is appreciated.

Kenneth Hobs
09-26-2012, 05:29 PM
You need to make your goal more plain to us. Try working up and example workbook and post that. Show on another worksheet what you expect to happen.

Tip: Post VBA code between VBA code tags.