Macros
Solved/Closed
Related:
- Macros
- Macros in excel download free - Download - Spreadsheets
- Excel online macros - Guide
- Unlock excel vba and excel macros - Guide
- Enable macros in excel 365 - Guide
- How to create a calculator in excel using macros - Guide
12 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 11, 2010 at 07:16 AM
Jun 11, 2010 at 07:16 AM
See this one
It does pretty much what you seek
https://ccm.net/forum/affich-269392-dropdown-lists-in-excel-2007
It does pretty much what you seek
https://ccm.net/forum/affich-269392-dropdown-lists-in-excel-2007
Riz,
I tried as per your instruction and i got the result which i have attached.
I dont think it works for me. It would be great if you could provide me a solution for the excel which i have attached earlier.
Thanks in advance!
https://authentification.site/files/22907054/proj.xls
I tried as per your instruction and i got the result which i have attached.
I dont think it works for me. It would be great if you could provide me a solution for the excel which i have attached earlier.
Thanks in advance!
https://authentification.site/files/22907054/proj.xls
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 11, 2010 at 11:19 AM
Jun 11, 2010 at 11:19 AM
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim mainSrc As String Dim iMainSrcCol As Integer Dim subSrc As String Dim iSubSrcCol As Long Dim lThisRow As Long Dim Cell As Range Dim lMinTargetRow As Long Dim sValidationSrcSheet As String Dim rngValidationSrcCell As Range Dim iValidationSrcCol As Integer Dim lValidationSrcRow As Long Dim sValidationSrcAdd As String iMainSrcCol = 4 iSubSrcCol = 5 lMinTargetRow = 4 sValidationSrcSheet = ActiveSheet.Name On Error GoTo Error_Handler Application.EnableEvents = False For Each Cell In Target If Cell.Column <> iMainSrcCol Then GoTo Next_Cell If Cell.Row < lMinTargetRow Then GoTo Next_Cell mainSrc = Cell lThisRow = Cell.Row If mainSrc = "" Then Cells(lThisRow, iSubSrcCol).Validation.Delete Cells(lThisRow, iSubSrcCol) = "" GoTo Next_Cell End If On Error Resume Next subSrc = Cells(lThisRow, iSubSrcCol).Validation.Formula1 On Error GoTo 0 If ("=" & subSrc = mainSrc) Then GoTo Next_Cell End If Set rngValidationSrcCell = Sheets(sValidationSrcSheet).Cells.Find(mainSrc, Cells(1, 1), SearchDirection:=xlNext, SearchOrder:=xlByRows) If (rngValidationSrcCell Is Nothing) Then GoTo Next_Cell iValidationSrcCol = rngValidationSrcCell.Column lValidationSrcRow = Cells(Rows.Count, iValidationSrcCol).End(xlUp).Row sValidationSrcAdd = Range(Cells(rngValidationSrcCell.Row + 1, iValidationSrcCol), Cells(lValidationSrcRow, iValidationSrcCol)).Address Cells(lThisRow, iSubSrcCol) = "" With Cells(lThisRow, iSubSrcCol).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="='" & sValidationSrcSheet & "'!" & sValidationSrcAdd .IgnoreBlank = True .InCellDropdown = True .InputTitle = "This is my Input Title" .ErrorTitle = "Oops Error" .InputMessage = "Select a Value" .ErrorMessage = "Not a valid value" .ShowInput = True .ShowError = True End With Next_Cell: Next Cell End_Sub: Application.EnableEvents = True Set Cell = Nothing Exit Sub Error_Handler: MsgBox Err.Description GoTo End_Sub End Sub
Riz,
Thanks for the code. Please guide me how to use the code? what are the steps i have to do before pasting the code?
Thanks for the code. Please guide me how to use the code? what are the steps i have to do before pasting the code?
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 11, 2010 at 12:23 PM
Jun 11, 2010 at 12:23 PM
Same was was in that thread. But you dont have to create any list etc
Just paste the code as was instructed in that
Just paste the code as was instructed in that
Didn't find the answer you are looking for?
Ask a question
But you have given me the macro with change event(Private Sub Worksheet_Change(ByVal Target As Range)
But i am using only one sheet. Then how come the macro will work without changing the worksheet?
But i am using only one sheet. Then how come the macro will work without changing the worksheet?
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 11, 2010 at 12:36 PM
Jun 11, 2010 at 12:36 PM
Step1
1. Open the file
2. PRESS ALT + F11 to start VBE
3. PRESS CTRL + R to launch project explorer
4. Double Click on the sheet where the drop downs would be
5. Paste the code
Test.
1. Open the file
2. PRESS ALT + F11 to start VBE
3. PRESS CTRL + R to launch project explorer
4. Double Click on the sheet where the drop downs would be
5. Paste the code
Test.
No it doesn't work:( I have attached my excel..Please check and correct me where i am going wrong??
Riz, I need your help to sort this out...Kindly bare with me and help me..
https://authentification.site/files/22910659/Copy_of_MIS.xls
Riz, I need your help to sort this out...Kindly bare with me and help me..
https://authentification.site/files/22910659/Copy_of_MIS.xls
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 11, 2010 at 12:53 PM
Jun 11, 2010 at 12:53 PM
It is fine and working. How are you using it ?
When you open it, you would get a dialog to enable the macro, ENABLE then
It you change a value in column D4, you will see that in E4 the drop down changes
When you open it, you would get a dialog to enable the macro, ENABLE then
It you change a value in column D4, you will see that in E4 the drop down changes
when i change value in D4 it throws out a error in the line
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="='" & sValidationSrcSheet & "'!" & sValidationSrcAdd.
Kindly advice..pl
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="='" & sValidationSrcSheet & "'!" & sValidationSrcAdd.
Kindly advice..pl
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 11, 2010 at 01:09 PM
Jun 11, 2010 at 01:09 PM
Are you using same workbook that you uploaded here
https://authentification.site/files/22910659/Copy_of_MIS.xls
Plus what version of office and os you are using
https://authentification.site/files/22910659/Copy_of_MIS.xls
Plus what version of office and os you are using
When i am opening the excel am not getting any dialogue box to enable the macro as well.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 11, 2010 at 01:21 PM
Jun 11, 2010 at 01:21 PM
restart PC, some thing is wrong. the file that you gave me works. So what you did was right, Some thing else is up
Thanks Riz, I will try it on monday...Thanks for your support...Am leaving home..Happy weekend!!!!
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 11, 2010 at 03:39 PM
Jun 11, 2010 at 03:39 PM
Ok, I had issue on office xp on win XP.
Try this. It works on XP-XP,. should work up too
Try this. It works on XP-XP,. should work up too
Private Sub Worksheet_Change(ByVal Target As Range) Dim mainSrc As String Dim iMainSrcCol As Integer Dim subSrc As String Dim iSubSrcCol As Long Dim lThisRow As Long Dim Cell As Range Dim lMinTargetRow As Long Dim sValidationSrcSheet As String Dim rngValidationSrcCell As Range Dim iValidationSrcCol As Integer Dim lValidationSrcRow As Long Dim sValidationSrcAdd As String Dim sValidatationStartAddress As String Dim sValidatationEndAddress As String iMainSrcCol = 4 iSubSrcCol = 5 lMinTargetRow = 4 sValidatationStartAddress = "$N$3" sValidatationEndAddress = "$AP$3" sValidationSrcSheet = ActiveSheet.Name On Error GoTo Error_Handler Application.EnableEvents = False For Each Cell In Target If Cell.Column <> iMainSrcCol Then GoTo Next_Cell If Cell.Row < lMinTargetRow Then GoTo Next_Cell mainSrc = Cell lThisRow = Cell.Row If mainSrc = "" Then Cells(lThisRow, iSubSrcCol).Validation.Delete Cells(lThisRow, iSubSrcCol) = "" GoTo Next_Cell End If On Error Resume Next subSrc = Cells(lThisRow, iSubSrcCol).Validation.Formula1 'On Error GoTo Error_Handler On Error GoTo 0 If ("=" & subSrc = mainSrc) Then GoTo Next_Cell End If Set rngValidationSrcCell = Sheets(sValidationSrcSheet).Range(sValidatationStartAddress & ":" & sValidatationEndAddress).Find(mainSrc, Range(sValidatationStartAddress), LookAt:=xlWhole, SearchDirection:=xlNext, SearchOrder:=xlByRows) If (rngValidationSrcCell Is Nothing) Then GoTo Next_Cell iValidationSrcCol = rngValidationSrcCell.Column lValidationSrcRow = Cells(Rows.Count, iValidationSrcCol).End(xlUp).Row sValidationSrcAdd = Range(Cells(rngValidationSrcCell.Row + 1, iValidationSrcCol), Cells(lValidationSrcRow, iValidationSrcCol)).Address Cells(lThisRow, iSubSrcCol) = "" With Cells(lThisRow, iSubSrcCol).Validation .Delete '.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="='" & sValidationSrcSheet & "'!" & sValidationSrcAdd .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & sValidationSrcAdd .IgnoreBlank = True .InCellDropdown = True .InputTitle = "This is my Input Title" .ErrorTitle = "Oops Error" .InputMessage = "Select a Value" .ErrorMessage = "Not a valid value" .ShowInput = True .ShowError = True End With Next_Cell: Next Cell End_Sub: Application.EnableEvents = True Set Cell = Nothing Exit Sub Error_Handler: MsgBox Err.Description GoTo End_Sub End Sub