LordDragon
08-22-2015, 08:06 PM
Greetings Excel Gurus,
I have another problem I need some help with please.
I'm running into a conflict with my project requirements. I have to be able to sort each system part list by any column on the sheet, and by color.
I have all those working, except the by color one.
But the problem I'm asking about here is the conflict which says that I need to "lock" certain items based on the project criteria.
For example: On the "AutoDriller" page, there are a few items that are either 110V or 220V. Which means that they have a different part number depending on the voltage the system runs on.
If the Project criteria says that the system is 110V, then I need the 220V item locked so it cannot accidentally be selected.
Before adding the "Sort" feature, this was working fine. I was locking and hiding the entire row that contained the item that shouldn't be ordered.
Now, I can't do that because once the sheet is sorted by something other than the default, whatever was in the hidden rows gets moved and then whatever is in that row now is not visible.
I removed the hidden rows part and attemepted to change the name of the item in the cell by declaring a name for that cell and then changing the item in the cell to match the item that should be used.
This also didn't work, because the cell name remained where it was set instead of following the item.
For example: on the AutoDriller page again. The items in question are the Control Box (110V) and (220V). They are on Rows 3 & 4 respectivley. I attempted to name the cell in B3 "ADRConBox" and the matching part number cell (D3) "ADRConBoxPN" I then had the following code:
If ActiveWorkbook.Worksheets("Project Info").VoltageBox.Value = strVolt1 Then
With ActiveWorkbook.Worksheets("AutoDriller")
.Range("ADRConBox").Value = "ADR Control Box (110V)"
.Range("ADRConBoxPN").Value = "ADR030"
End With
This does change the current item in cells B3 and D3 to the desired value. However, if the sheet is not default sorted and is sorted by say, part number. Then the item in those cells is no longer the desired item and therefore should not be changed.
I need a way to "find" the part number in column D and then change the rest of the values on that row to match the desired item.
I've looked into VLOOKUP, but it states that the item being searched for has to be in the first column and I have not found a way to change that.
I have tried looking into the .Find feature, but I haven't found much about it, yet.
Any help or suggestions would be appreciated.
I have attached a copy of my workbook in case that will help. If you use the workbook, I have added two tabs to the ribbon that have the controls for most my macros.
Thanks.
I have another problem I need some help with please.
I'm running into a conflict with my project requirements. I have to be able to sort each system part list by any column on the sheet, and by color.
I have all those working, except the by color one.
But the problem I'm asking about here is the conflict which says that I need to "lock" certain items based on the project criteria.
For example: On the "AutoDriller" page, there are a few items that are either 110V or 220V. Which means that they have a different part number depending on the voltage the system runs on.
If the Project criteria says that the system is 110V, then I need the 220V item locked so it cannot accidentally be selected.
Before adding the "Sort" feature, this was working fine. I was locking and hiding the entire row that contained the item that shouldn't be ordered.
Now, I can't do that because once the sheet is sorted by something other than the default, whatever was in the hidden rows gets moved and then whatever is in that row now is not visible.
I removed the hidden rows part and attemepted to change the name of the item in the cell by declaring a name for that cell and then changing the item in the cell to match the item that should be used.
This also didn't work, because the cell name remained where it was set instead of following the item.
For example: on the AutoDriller page again. The items in question are the Control Box (110V) and (220V). They are on Rows 3 & 4 respectivley. I attempted to name the cell in B3 "ADRConBox" and the matching part number cell (D3) "ADRConBoxPN" I then had the following code:
If ActiveWorkbook.Worksheets("Project Info").VoltageBox.Value = strVolt1 Then
With ActiveWorkbook.Worksheets("AutoDriller")
.Range("ADRConBox").Value = "ADR Control Box (110V)"
.Range("ADRConBoxPN").Value = "ADR030"
End With
This does change the current item in cells B3 and D3 to the desired value. However, if the sheet is not default sorted and is sorted by say, part number. Then the item in those cells is no longer the desired item and therefore should not be changed.
I need a way to "find" the part number in column D and then change the rest of the values on that row to match the desired item.
I've looked into VLOOKUP, but it states that the item being searched for has to be in the first column and I have not found a way to change that.
I have tried looking into the .Find feature, but I haven't found much about it, yet.
Any help or suggestions would be appreciated.
I have attached a copy of my workbook in case that will help. If you use the workbook, I have added two tabs to the ribbon that have the controls for most my macros.
Thanks.