Excel dropd down menu

Microsoft Excel 2007
December 26, 2009 at 18:37:53
Specs: Windows XP
Hi!
I am making a map that shows each station at my office. I already made a map of it on excel. I am trying to make it so with it people can tell me when a PC has an issue.

Any tips?

I am trying to make it so that they can choose from a drop down menu what it is and then that colors the sahde.

I had soe alternative ideas, but my knowdledge of VBA is limited.

It is possible to make it to edit anotehr workbokk right? so I can have a log there?

Would it be possible to lock it after they ahve chosen something?

thnx for replying to anyone who does.


See More: Excel dropd down menu

Report •


#1
December 26, 2009 at 22:29:25
I think you need to be a lot more specific.

re: I am trying to make it so that they can choose from a drop down menu what it is and then that colors the sahde.

Colors what shade?

(I'm assuming you know how to create a drop down menu. If not, let us know and we'll explain how it's done.)

re: It is possible to make it to edit anotehr workbokk right?

If by "it" you mean VBA, the answer is a qualified "Yes". You need to tell us what your specific goals are before we can offer any assistance.

re: Would it be possible to lock it after they ahve chosen something?

Lock what?

The drop down? The other workbook? VBA?

You've mentioned too many items in your post for us to know which "it" you are referring to.


Report •

#2
December 26, 2009 at 22:55:59
Thank you for your time and patience. Sorry for the massive typ0s.

I do know how to create the drop down menu, thank you.

I meant shape. I have this map of the PCs, lets say someone has a BSOD or a no power issue. We are trying to improve the way that they let us know.

What may be useful is that when they select an option of the drop down menu it fills the cell red. I know it is possible, but have not applied myself to read how.

As for the edit another workbook. I mean that when the end user makes the change on the shared sheet. it edits another workbook that we have. at that point, can that create an email if configured with outlook or something? (I don't know if all this is possible...)

I meant lock the cell.So lets say end user 'A' chooses one of the options. I would not want them to reset that or change another cell that end user 'B' has changed. can privileges be set?

====================

Thank you for replying!
Thank yo for your patience!


Report •

#3
December 27, 2009 at 07:37:46
Please review this site to see the features that are unavailable in a shared workbook:

http://office.microsoft.com/en-us/e...

While some of the things you want to do will work in a shared workbook, the locking of the cells might be an issue.

In order to lock a cell, you have to have the workbook protected. In order to lock cells after uses changes them, you have to start with the sheet protected and the cells unlocked. After the data is entered, you have to unprotect the sheet, lock the cell and protect the sheet again.

Unfortunately, one of the features that is not available in a shared workbook is the ability to change the protection. You would need to unshare the workbook, unprotect the sheet, lock the cell, protect the sheet and then share the workbook again. This can be done with VBA, but it's dangerous and cumbersome.

The problem occurs when more than one person has the shared workbook open and you remove the sharing, unprotect it, etc. Depending on what users do during the unshared/unprotected period, you could have serious problems.

Just as a rough example, the code below will unshare the workbook, unprotect it, lock a cell in A1:B16 if it gets changed and then protect and share the workbook again.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was within A1:B16
  If Not Intersect(Target, Range("A1:B16")) Is Nothing Then
'If the workbook is Shared, Unshare it
   If ActiveWorkbook.MultiUserEditing Then
     ActiveWorkbook.ExclusiveAccess
   End If
'Unprotect the sheet
     ActiveSheet.Unprotect
'Lock the cell that was changed
      Target.Locked = True
'Protect the sheet
     ActiveSheet.Protect
'Save and Share the workbook
      ActiveWorkbook.SaveAs "Book1.xls", , , , , , xlShared
  End If
End Sub


Report •

Related Solutions

#4
December 27, 2009 at 08:25:04
Hi,

Regarding automatically e-mailing from Excel, see Response #1 in this post.

Regards


Report •

#5
December 27, 2009 at 12:09:51
Awesome!
Let me read all this and i will be back in a day.

another question in the meantime

the map i did has several cells grouped , would that affect any of the mentioned!

http://i587.photobucket.com/albums/...


Report •


Ask Question