few datavalidated cells not working after after adding combobox. for INDIRECT formula

[Prity]

Hi All,Its quite urgent help me outIn my project there are some 100 cells with the data validations used. now i have implemented the dropdown size increase n number of values showing to 30 with the help of the link ---http://www.contextures.com/xlDataVal14.htmlBut its not working for some of the 20 cells in the sheet which has the following formula:=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B157," ","_"),"HQ","_HQ"),"%",""))its not showing any value in the combo box for these datavalidated cells.If i remove the combo box then i am able to see the values.Please help me out.But i need the combo boxKind regardsSeema

[Prity]

Hi All,


Its quite urgent help me out


In my project there are some 100 cells for with the data validations used. now i have implemented the dropdown size increase n number of values showing to 30 with the help of the link ---http://www.contextures.com/xlDataVal14.html


But its not working for some of the 20 cells in the sheet which has the following formula:
=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B157," ","_"),"HQ","_HQ"),"%",""))
its not showing any value in the combo box for these datavalidated cells.


If i remove the combo box then i am able to see the values.


Please help me out.


Kind regards
Seema

[Bob Phillips]

Post the workbook with the combobox at fault.

[Prity]

HI xld,the excel file is quite big i will try to attach it.

[Prity]

Hi xld,

I uploaded the workbook.in the following link.
http://speedy.sh/qf5sJ/SPS-v7-Resource-Ctry-changes1-Copy.zip

click on the link. n select slow download.
in this workbook there are so many sheets - select the - Services sheet. in this sheet in column C(Role) i am not able to see any value. So please help me out with this.

NOTE: assword for - unprotect sheet -GORDO
to view code -phat

Thanks in advance

Regards
Seema

[p45cal]

I've been looking at the file and things appear OK at first glance, but maybe I'm not looking in the right places.
Can you give an example of one cell in column C, saying what you've selected in the cell immediatlely to its left (column B) then saying what the cell in column C is showing in the dropdown and what should be showing in the dropdown?

[Bob Phillips]

Try deleting all of the *.exd files on your system, then reboot.

[Prity]

Hi xld,

How to delete those *.exd files? Please guide me.
well Xld, did my workbook ran on ur system?


Hi Pascal,

Please follow the steps:
1. when you open the work book, u can find in the Bottom of the workbook , the third sheet by name - Services sheet. in this sheet

2.In column B - select any country.(MANDATORY, AFter this only u can go to column C)

3. go to ---column C(Role)
2. In column C - i am not able to see any value. So please help me out with this.


Thanks in advance
Seema

[Bob Phillips]

Details here (http://excelmatters.com/2014/12/10/office-update-breaks-activex-controls/)

No it didn't work for me. It seems it did for Pascal, that is why I thought of the ActiveX issue.

[p45cal]

It looks like the combobox doesn't like a horizontal .listfillrange.
I'm looking at this on and off (other things to do)..

[p45cal]

Try making this the sheet's SelectionChange event handler:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mystr As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Object.Clear
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
'Cancel = True
Application.EnableEvents = False
'get the data validation formula
If Target.Column = 3 Then
mystr = Replace(Replace(Replace(Replace(Target.Offset(, -1).Value, " ", "_"), "&", "_"), "HQ", "_HQ"), "%", "")
Else
mystr = Target.Validation.Formula1

mystr = Right(mystr, Len(mystr) - 1)
End If
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 55
.Height = Target.Height + 8
If Sheets("Rate Table").Range(mystr).Cells.count = 1 Then
.ListFillRange = mystr
Else
If Target.Column = 3 Then
.Object.List = Application.Transpose(Sheets("Rate Table").Range(mystr).Value)
Else
.Object.List = Sheets("Rate Table").Range(mystr).Value
End If
.LinkedCell = Target.address
End If
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown

End If

errHandler:
Application.EnableEvents = True
End Sub
I changedthe variable name str to mystr since Str is a function in vba and I wanted to avoid confusion.

[Prity]

Hi Pascal,

The code, is working only for column B, Column C(Role) but it affected other all combo boxes(with data validations) :-( , i mean none of the other combo boxes are showing any values, like in column D and other columns where data validation is there.in column D,AF,AN,AO,AP,AQ,AR,AS,AT. these are not showing any combo boxes, which they used to show previously.....

So i want the combo box with the the values to be showed for all these cells with data validation.

If you find some way, let me know...

Thanks for the above try, u gave ur time for my issue... thank you...


Regards
Seema

[p45cal]

try:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mystr As String, myRng As Range
Dim cboTemp As OLEObject

Set cboTemp = Me.OLEObjects("TempCombo")
'On Error Resume Next
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Object.Clear
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
If InStr(Target.Validation.Formula1, "INDIRECT") > 0 Then
mystr = Replace(Replace(Replace(Replace(Target.Offset(, -1).Value, " ", "_"), "&", "_"), "HQ", "_HQ"), "%", "")
Else
mystr = Target.Validation.Formula1
mystr = Right(mystr, Len(mystr) - 1)
End If
Set myRng = Evaluate(mystr)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 55
.Height = Target.Height + 8
If myRng.Cells.count = 1 Then
.ListFillRange = mystr
Else
If myRng.Columns.count > 1 Then
.Object.List = Application.Transpose(myRng.Value)
Else
.Object.List = myRng.Value
End If
.LinkedCell = Target.address
End If
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
End If

errHandler:
Application.EnableEvents = True
End SubA major assumption is that any cell with data validation whose formula1 contains "INDIRECT" is referring to the cell directly to the left and uses the equivalent of:
=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(cellref," ","_"),"&","_"),"HQ","_HQ"),"%",""))
in the vba code to determine the named range.

[Prity]

Hi Pascal,

I the dropdowns are showing all the values, thank you. But i got another issue which affected the - Column H n Column N.

When user select the values from column B n C then the values in the Column H n N should get updated automatically.

If i select the value from combobox it wont update the values in the H & N. But when i reduced the size of dropdown, n selected from datavalidated Cell B or c then the values are appearing...

How to resolve this issue...

Thanks Pascal


Regards
Seema

[p45cal]

This is a separate problem. I'm not sure why things aren't updating as you want them to; there could be several reasons:
1. The combobox updating the LinkedCell doesn't trigger a worksheet_change event (a workaround may be required)
2. Code which might affect the precedent cells to the formulae in columns H and N (eg. one of the precedent cells is in column DA, this is column number 105 and there is code which will alter that value; is that code working properly?)
3. In the worksheet_change event for the Services sheet there are many references to the ActiveCell, what frightens me here is that the active cell could be on any row depending on what the user does after he has changed a value in a cell (does he click on another cell (perhaps several rows and columns away from the changed cell), or does he press the left/right/up/down cursor keys, or does he press the Enter key (where does the active cell go after pressing Enter?). Shouldn't Target be used?
4. other reasons?

It just takes too much time for me to analyse/unravel what's going on, all the while only able to guess what you intend.
The complexity of this project is such that you should (IMO) seek paid-for professional help.

[Prity]

HI Pascal,

I got the code where the changes are happening, for the column
H n N are in the following function.....
Call ComputeRate(ActiveCell.Value, Cells(ActiveCell.Row(), 2).Value) '--- now the value is present in the column 2 not in 26 so I changed it to 2.


But can u please guide me where actually i should place this piece of code....

Thanks in Advance
Seema

[Prity]

0

[p45cal]

i need one small help from u, if u can. Just spend ur 5 or 10min of ur precious time. actually i am not getting where to call that function in the - Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Probably not a good idea to put it into the selection_change event, but if you were to you'd have to have have the same checks before calling it as there are in worksheet_change event viz.:

If InRange(ActiveCell, Range("UISROWS")) _
Or InRange(ActiveCell, Range("UISndELRows")) _
etc.
Perhaps in the TempCombo_KeyDown event handler? However I seriously question the use of activecell: in the worksheet_change event it's the cell you've just moved to after changing another cell, and in the selection_change event it should be all right but be aware if the code itself might change the active cell.

My time isn't any more precious then the next man's, it's just that your requests need such a lot of it (I have to check exactly what the macro does, what the procedures it calls do, make sure it won't impact negatively on the sheet (and that's very difficult because I have little understanding of what's vital/important in your workbook), nor do I know what you want to happen - so it's not just 5 or 10 minutes; you workbook has some 45 sheets in it, many protected, some 16 code modules, some 20 userforms, 664 procedures, and getting on for 10,000 lines of code.

[Prity]

Hi Pascal,,

Again i kept on trying for this issue, well its not getting fixed atall... :-(

I dont no why the - TempCombo_KeyDown

I pasted the code in to the - Tempcombo_change() --- dint not work.

well one more solution i tried as below:



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim mystr As String, myRng As Range
Dim cboTemp As OLEObject
Set cboTemp = Me.OLEObjects("TempCombo")
'On Error Resume Next
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Object.Clear
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
If InStr(Target.Validation.Formula1, "INDIRECT") > 0 Then
mystr = Replace(Replace(Replace(Replace(Target.Offset(, -1).Value, " ", "_"), "&", "_"), "HQ", "_HQ"), "%", "")
'Call ComputeRate(ActiveCell.Value, Cells(ActiveCell.Row(), 2).Value)

If InRange(ActiveCell, Range("UISROWS")) _
Or InRange(ActiveCell, Range("UISndELRows")) _
Or InRange(ActiveCell, Range("UGSROWS")) _
Or InRange(ActiveCell, Range("UTSROWS")) Then
Call ComputeRate(ActiveCell.Value, Cells(ActiveCell.Row(), 2).Value) '--- now the value is present in the column 2 not in 26 so I changed it to 2
' Call ComputeRate(ActiveCell.Value, Cells(ActiveCell.Row(), 26).Value)
ElseIf InRange(ActiveCell, Range("UISRC")) _
Or InRange(ActiveCell, Range("UISNDRC")) _
Or InRange(ActiveCell, Range("UTSRC")) _
Or InRange(ActiveCell, Range("UGSRC")) _
Or InRange(ActiveCell, Range("TPResCnt")) _
Or InRange(ActiveCell, Range("TPHCRC")) _
Then
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="GORDO"
Cells(ActiveCell.Row(), 3).Select ' --- from this line lot of changes like if its 2 then do it as 3, If its F change it to G
Selection.ClearContents
Cells(ActiveCell.Row(), 6).Select
Selection.ClearContents
rolfor = "C" & ActiveCell.Row() & ":F" & ActiveCell.Row()
Range(rolfor).Font.Bold = False
' Selection.ClearContents
Range("G" & ActiveCell.Row).Font.Bold = False
Cells(ActiveCell.Row(), 106).Value = "" ' changed it too 106 --Cells(ActiveCell.Row(), 105).Value = ""
Cells(ActiveCell.Row(), 107).Value = "" ' changed it too 107 ---Cells(ActiveCell.Row(), 106).Value = ""
Range("D" & ActiveCell.Row).Select
ActiveSheet.Protect Password:="GORDO"
Application.EnableEvents = True
Application.ScreenUpdating = True
ElseIf InRange(ActiveCell, Range("TPRows")) _
Or InRange(ActiveCell, Range("TPHRows")) _
Then
Call ComputeRateTP(ActiveCell.Value, Cells(ActiveCell.Row(), 42).Value) ' here i changed it from 41 to 42... Initially it was --- like -- Call ComputeRateTP(ActiveCell.Value, Cells(ActiveCell.Row(), 42).Value)..
End If
'O&S Section; checks if the changed area is either resource country or role
If InRange(ActiveCell, Range("UIS_OSRows")) _
Or InRange(ActiveCell, Range("UISND_OSRows")) _
Or InRange(ActiveCell, Range("UGS_OSRows")) _
Or InRange(ActiveCell, Range("UTS_OSRows")) Then
Call ComputeOSRate(ActiveCell.Value, Cells(ActiveCell.Row(), 2).Value) 'as we inserted the column B the colum'AL'(its number is 38) became column'AM'(AM is 39), so now we did cut n paste to column B, so changed it to 2... Call ComputeOSRate(ActiveCell.Value, Cells(ActiveCell.Row(), 38).Value)
ElseIf InRange(ActiveCell, Range("UISOS_RC")) _
Or InRange(ActiveCell, Range("UISNDOS_RC")) _
Or InRange(ActiveCell, Range("UTSOS_RC")) _
Or InRange(ActiveCell, Range("UGSOS_RC")) _
Or InRange(ActiveCell, Range("TPHOS_RC")) _
Or InRange(ActiveCell, Range("TPLOS_RC")) _
Then
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="GORDO"
Cells(ActiveCell.Row(), 3).Select
Selection.ClearContents
Cells(ActiveCell.Row(), 6).Select
ActiveSheet.Unprotect Password:="GORDO"
Selection.ClearContents
rolfor = "C" & ActiveCell.Row() & ":F" & ActiveCell.Row()
Range(rolfor).Select
' Selection.ClearContents
ActiveSheet.Unprotect Password:="GORDO"
Selection.Font.Bold = False
Range("G" & ActiveCell.Row).Font.Bold = False
Cells(ActiveCell.Row(), 112).Value = ""
Cells(ActiveCell.Row(), 113).Value = ""
Range("D" & ActiveCell.Row).Select
ActiveSheet.Protect Password:="GORDO"
Application.EnableEvents = True
Application.ScreenUpdating = True
ElseIf InRange(ActiveCell, Range("TPL_OSRows")) _
Or InRange(ActiveCell, Range("TPH_OSRows")) _
Then
Call ComputeOSRateTP(ActiveCell.Value, Cells(ActiveCell.Row(), 49).Value) 'Original Code ---'Call ComputeOSRateTP(ActiveCell.Value, Cells(ActiveCell.Row(), 48).Value)
End If

'ActiveCell.Activate
Else
mystr = Target.Validation.Formula1
mystr = Right(mystr, Len(mystr) - 1)
End If
Set myRng = Evaluate(mystr)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 55
.Height = Target.Height + 8
If myRng.Cells.count = 1 Then
.ListFillRange = mystr
Else
If myRng.Columns.count > 1 Then
.Object.List = Application.Transpose(myRng.Value)
Else
.Object.List = myRng.Value
End If
.LinkedCell = Target.address
End If
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
End If



errHandler:
Application.EnableEvents = True

End Sub





i tried the above code....
But the main thing is that - the all the values are being shown but the sheet is flickerring a lot...
and the values are shown later , when we come out of the dropdown



Can you please have a look, and please help me out.

Regards
Seema

[p45cal]

Flickering: try moving the Application.ScreenUpdating = False line up to just below the line On Error GoTo errHandler
"and the values are shown later , when we come out of the dropdown": No, it would take me too long for me to do it properly (ensuring no consequent problems of my alterations/additions).

[Prity]

i got the solution



Private Sub TempCombo_Change()
ActiveCell.Value = Me.TempCombo.Value
End Sub


regards
Seem