Drop down list to define name of cell NOT value

I'm trying to create a drop down list that will define the name of the cell so I can reference it in formulas or codings to change the format of various cells defined by a specific name.

I'm not even sure if I can have multiple cells defined as the same name, but maybe if I try and tell you what I'm trying to do, I can get advice on how to do it. I understand the very basics of VBA, so a macro to run would also be helpful.

I'll be inputting data into cells, and I want the color of the cell to change depending if the data is one of these criteria: 'In Service', 'In Transit', or 'Must Purchase'. I can't just make separate criteria columns to list all the data, because the data is already placed in a specific order. So I want whoever is viewing this to know which entry fits which criteria from the color of the cell.


The only thing I could think of is inputting the cell data value, and a drop down menu to define the cell name. I'll input a conditional formatting function to format based on the defined name criteria.


Please let me know what you guys think! Thanks!

Hi aantonio9 and welcome to ExcelForum,

You can define a 'Named Range' manually (Excel 2003 syntax):
Insert > Name > Define (allows add, change or delete)

You can add (or modify a named range) using VBA as:

Names.Add ("xxx"), Range("B1:C6")

If you want to use the 'Named Range' in VBA:

  Dim myRange As Range
  Set myRange = Range("xxx")
  Debug.Print myRange.Address   'Writes to Immediate Window in VBA (CTRL G)

  'or
  
  Debug.Print Range("xxx").Address   'Writes to Immediate Window in VBA (CTRL G)

If you ever want to delete a 'Named Range' in VBA:

Names("xxx").Delete

Lewis

Hi Lewis,

Thanks for the input! I'm still a little confused though cause I'm not sure if that code pertains. I can't define a range as a certain name, because I don't have a specific range. The excel sheet will be going around and so I won't know what range to even put, which is why I wanted a drop down list for users to define the name of the cell on their own. This will then automatically change the cells format depending on the defined name for the cell value the user inputs.

I don't know if what I'm saying is clear to understand, but let me know if that makes sense! Thank you again!

I would understand what you need better if you posted a small sample workbook, with a short description of what is supposed to happen inside the workbook.

You can dynamically have user's select a Cell. See the attached file with complete code that follows that selects a 'Special Cell' when the User 'Double Clicks' that cell.

I hope this makes things clearer and not more confusing.

Lewis

ThisWorkbook Code:

Option Explicit

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  
  'Return focus of the 'Double Clicked' Cell to the user
  Cancel = True
  
  'Process the 'Double Click Event'
  Call ProcessDoubleClickEvent(Sh, Target)
  
End Sub

Ordinary Module 'ModSpecialCell' Code:

Option Explicit

Private myGblSheetName As String
Private myGblCellAddress As String

Sub ProcessDoubleClickEvent(ByVal Sh As Object, ByVal Target As Range)

  'Get the Sheet Name of the Double Clicked Cell
  myGblSheetName = Sh.Name
  
  'Get the Cell address of the Double Clicked Cell (without '$' signs)
  myGblCellAddress = Target.Address(False, False)

  MsgBox "Special Cell Selected." & vbCrLf & _
         "Sheet Name: " & myGblSheetName & vbCrLf & _
         "Cell Address: " & myGblCellAddress

End Sub


Sub SomeLaterProcessing()

  If Len(myGblSheetName) > 0 Then
    MsgBox "Processing with User Special Cell." & vbCrLf & _
           "Sheet Name: " & myGblSheetName & vbCrLf & _
           "Cell Address: " & myGblCellAddress
  Else
    MsgBox "Processing Not Allowed." & vbCrLf & _
           "User must first 'Double Click' a Special Cell."
  End If
  

End Sub

I have attached an image of what I'm looking to replicate in excel. I'm trying to input it into excel, but I'm having problems doing so and having it come out the way I want.


I want users to be able to input the inventory number and the date it will run for the machine specified (CT-A,B,C...). But I want excel to only show the months of the year just as it is shown in the image. The line separators in between the inventory number are estimated dates at which the machine parts will run.

I know that is separate from the issue, which I will create another thread for.


What I was originally trying to work out was that users will input the inventory number and I want them to be able to select whether or not the part is in Alaska, Transit, Factory, or Needed so that the cell color will change automatically. That way users don't have to manually go in each time and change the color themselves as they will be inputting a lot of data.

I hope this isn't too complicated.

Let me know what you think. Sorry for the late reply, I've been out of town.

Extremely grateful for the help.

Hi,

I wasn't sure what you wanted in your jpg file. See the attached file, which hopefully is a good first attempt to satisfy your needs. If you 'double click' on a cell in the table, the cell changes to the 'Current Status Color'. All status changes and data changes in the table are logged on Sheet 'Log'.

Please let me know if you have any question, problems, or want me to modify the file to better suit your needs.

When you change and/or mode cells in a worksheet, VBA cell definitions must be changed. If you move things around, you must edit one or more of the following items in Module 'ModSpecialCell':

Public Const sDoubleClickStatusCELL = "F16"
Public Const sColorCodeCELL = "I18"

Private Const nMonthROW = 22
Private Const sItemCOLUMN = "A"
Private Const sColorCodeRANGE = "A9:A13"
Private Const sSpecialCellRANGE = "B23:M34"

Lewis

Lewis you are amazing!!! That's almost perfect to what I want that I can add to my coding that I've been messing with. You had some really great ideas that I'd like to incorporate.

What I'm trying to figure out now is if theres a way to input data into the table corresponding to specified dates? Like some item numbers will not be in use just for Jan, it may be used in the middle of Jan - only the beginning of march, and then also in march another item will be run? Hopefully that makes sense.

My original plan was to run it as a gantt chart, but modified ofcourse, but I can't display the item numbers as nicely as you have all on the same row with its unit number CT-A,B,C,etc.

Lewis you have helped me out so much and I'm so grateful!

What you might be able to do is stretch the months, to 3 or 4 cells per month.
If you used 3 cells per month you could use days 1-10, 11-20,21-31.
If you used 4 cells per month you could use the 1st full week, the 2nd full week, the 3rd full week, and the 4th would be the remainder of the month.

I'm glad it worked out. Please let me know if you have any questions and/or need any additional help.

Lewis

Lewis is there a way for users to input item #'s and their dates being used and have it show up on the table automatically similar to the concept I have done in my attached example worksheet?

That was the original gantt chart that I had previously talked about that I wasn't able to achieve similar table results as yours, which yours is the concept I'm looking for!

I also tried adding a spin button to display only inventory for that year, that way users can see inventory through the years. I tried linking it to the dates shown, but it ended up just changing my dates. Let me know if it's possible and if it's a good idea or if I should go about it a different way!

Hi,

Sorry for the delay, but your requests were not trivial, and required a lot of AutoFilter research. Sometimes I just fall in love with a project, and yours is one of those. You have an excellent spreadsheet design, and your application is full of technical challenges. The attached file demonstrates part of what is possible, but also has certain restrictions at the current time:
a. Tables can't be moved - Their locations are currently written in stone (but will be flexible in the future).
b. I accidentally destroyed your beautiful charts. I'm working on integrating chart capability into what I've done, that should look like what you want.

is there a way for users to input item #'s and their dates being used and have it show up on the table automatically

I implemented Add, Edit, or Delete Table Data using a UserForm.

I also tried adding a spin button to display only inventory for that year, that way users can see inventory through the years. I tried linking it to the dates shown, but it ended up just changing my dates. Let me know if it's possible and if it's a good idea or if I should go about it a different way!

I implemented User Selection of Table Display to include:
a. Table To AutoFilter
b. Choice of Start Date or End Date
c. Choice of Year (or display all).

Please note that Excel 'AutoFilter' apparently works on only One Table per Worksheet at a time. That is one of the reasons I added an additional sheet. Also note that since 'AutoFilter, hides rows, if you have two tables sharing rows, AutoFilter on one table falsely appears to be applying 'AutoFilter' to the other table.

Custom time periods can also be implemented if you want that capability.

Other items of interest:
1. I added two additional tables CT-C and CT-D on 'Sheet2'.
2. I added a Master List of Tables available, and a Master list of 'String No' values for each table. These items are at the bottom of Sheet1, but in the future (if you like the concept) they can go on a separate Sheet which can be hidden from view.
3. If you 'Double Click' cell 'A1' in Sheet1 or Sheet2, the focus immediately returns to the other Sheet.

Please let me know what you like, and what you don't like. If you want me to continue, it would be helpful if you uploaded a file formatted the way you want. Please note that I am using Excel 2003, which does not have all the capabilities of your version of Excel, and certain features (e.g. conditional formatting of more than 3 formulas in a cell) are excluded from my version. That means, when you want to use Excel files created in your version, I will have to provide installation instructions, on how to implement what I've done.

Lewis

You have upgraded mine a TON and it's coming a long so nicely! I'm running Excel 2010 by the way.

I love the inputting feature! Now the main and hardest part for me is having the inventory strings inputted on the chart the same format as the original chart you made with the months listed, the quarter, the machine CT A,B,C,etc. and then having the coressponding strings shown on the graph for each machine - basically all formatted in one graph the way your orignal chart was formatted.

Also, the machine name may change and there may be more machines added in the future or deleted. Is there a convenient way to edit the machine names, add, delete etc? For example purposes I put CT A, B, C... since I wasn't sure which machines we were running at the time. Let me know if I'm not too crazy! Thank you again Lewis!

Hey Lewis,

I have attached my proposed format and labeled notes and specifications in red/italicized. More details will be on the spreadsheet!

The attached sheet is only formatting. Ofcourse, it is an "in a perfect world" that would be my ideal Inventory Worksheet. I am perfectly okay with any modifications to suit what will work best and easiest for you and any ideas you may have.

I seperated each unit to have its own spreadsheet. I thought that may be easier to code.

I am completely understanding of the project time! I was hoping to get it done before the summer ends - end of August, but I understand if there will be more time needed. Thank you so much for your time and input Lewis!

Let me know if you have any questions or concerns!


Alex

Hi Alex,

Excellent design and very clear instructions on what you want.

I should have the preliminary coding with the formatting that you want done by the end of the weekend. This is being posted to let you know that progress is being made.

Lewis

Hi again Alex,

The preliminary coding was not in ready for prime time. I should be done with everything by the end of this weekend. I will upload when the file is ready to be seen by others.

Lewis

Hi Alex,

I'm interested in getting your feedback about the attached file. Every item is negotiable. I used
your input and built things the way I thought a user would find most efficient. Since I'm a perfectionist,
I think there's a lot more to be done before the file is ready to be seen, but if I don't post it now, nobody but me will ever see the file. I'm about 85% done, but still have about two more weeks of things to fix, before I would be satisfied. The last items usually take the longest amount of time. Since I have plenty to do, there is no need for you to rush your evaluation. You can give me your comments all at once or a little at a time.

The Administrative Menu allows you to Add a sheet, etc.
Sheet Master has several software switches you can try (highlighted in pale green).
Use cell A13 in the Master sheet to switch to the Production mode (hide the Master Sheet and the Item Template Sheet).


Major Changes:
a. I had to add two sheets (described below).
b. I had to add 5 extra data columns to the 'Item Sheet':
(1) MidLifeTimeDate (visible)
(2) Email Sent Date (visible) - If the cell contains "N/A" or any other text an Email will never be sent. This is useful when setting up the data for the first time, and some 'MidLifeTime' dates have already expired.
(3) Start Year (not visible - in normal use)
(4) End Year (not visible - in normal use)
(5) Start Year or End Year(not visible - in normal use)
NOTE: Items (3) thru (5) required for 'AutoFilter'

The attached file has the following major features:
a. Add/Edit/Delete a String No
b. Add an Item Sheet
c. Delete an EMPTY Item Sheet
d. AutoFilter based on one of:
(1) Start year or End Year (e.g. Shape No has start date or has end date in 2013).
(2) Start year
(3) End Year
(4) All years
e. Create Gantt Chart for ONE YEAR based on the 'AutoFilter' criteria.
f. Create a List of Items on the Gantt Chart
g. Send one Email (when the workbook) is opened for each StringNo whose MidLifeTime has expired, and hasn't previously had an Email sent.

I do not have 'Outlook' on my computer, so you will have to test the Email feature for me. This may be a painful process, so please be patient.
Use the Administrative Menu to test the e-mail feature:
a. Test e-mail uses the address on Sheet 'Master', cells C18 and C19.

The Sheet structure is as follows:
a. Inventory Sheet
b. Followed by One of more 'Item' Sheets'
c. Item Sheet Template (hidden in normal use)
d. Master (Control) Sheet (hidden in normal use) that contains:
(1) Master Email text
(2) Cells to permanently store customizable options
(3) Cells to store scratch data
(4) Notes and/or Instructions

The following special features are available:
a. On an 'Item Sheet':
(1) 'Double Click' on cell 'A1' to go to the 'Inventory' Sheet'
(2) 'Double Click' on an cell for a 'Data Item' to Open the UserForm to edit that data line.
b. On the 'Inventory' Sheet:
(1) 'Double Click' on cell 'A1' to go to the 'First' Item Sheet
(2) 'Double Click' on cell at the bottom that contains an 'Item Name' to go to that Item Sheet.
(3) 'Double Click' on cell at the bottom that contains a 'String No' to Open the UserForm to edit that 'String No'.
(4) The colors used will be the colors in cells 'D5 thru D8'. You can make those colors anything you want.

Undesirable Features of NOTE:
a. Temporary - The file is saved each time the Chart is generated (required to enable double click from list when workbook is opened). Fix being worked on.
b. Automatic Chart Update is not yet enabled.
c. Chart PrettyPrint display not complete - Black lines around chart in Column 'AB' and at bottom, and white background.
d. Occassional big red rectangle on chart.
e. Outlook warning message

Lewis

See the attached file which corrected all known errors I was able to correct.

Lewis