Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
9 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Selecting a cell
Posted on 2013-06-14
Hi,
I am tring to get a form to load and fill some txtboxes with certain text, depending on the selected cell which are all named (cMakita, cELC) etc.
The code I have got so far is attached but I cannot get it to work. I think it maybe the active cell or target line.
As always any help is much appreciated.
Thanks
I am tring to get a form to load and fill some txtboxes with certain text, depending on the selected cell which are all named (cMakita, cELC) etc.
The code I have got so far is attached but I cannot get it to work. I think it maybe the active cell or target line.
As always any help is much appreciated.
Thanks
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrHandler
Dim a As Range
Dim b As Range
Dim c As Range
Dim d As Range
Set a = Range("cMakita")
Set b = Range("cIndesit")
Set c = Range("cMilsco")
Set d = Range("cELC")
Select Case Target.Address
Case a
Load MainOrder
MainOrder.txtCust = "MAKITA"
MainOrder.cboProd.RowSource = ("tblMakita")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case b
Load MainOrder
MainOrder.txtCust = "INDESIT"
MainOrder.cboProd.RowSource = ("tblIndesit")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case c
Load MainOrder
MainOrder.txtCust = "MILSCO"
MainOrder.cboProd.RowSource = ("tblMilsco")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case d
Load MainOrder
MainOrder.txtCust = "ELC"
MainOrder.cboProd.RowSource = ("tblElc")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case Else
'do nothing
End Select
Exit_ErrHandler:
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume Exit_ErrHandler
End Sub
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
7-
6
13 Comments
Active today
Do you know how to use Debug? If not (or maybe even if you do) take a look at this article of mine. In any case put a breakpoint on line 11 (click in the left-hand margin) and when the code gets there, what is the value of Target.Address and does it match one of your named ranges a, b, c or d?
BTW don't worry that the article is amid at VB6. It's basically the same in VBA.
BTW don't worry that the article is amid at VB6. It's basically the same in VBA.
Active today
BTW the following probably won't work because a form has to be visible before you can set focus to a field, so reverse them.
MainOrder.cboProd.SetFocus
MainOrder.Show
MainOrder.cboProd.SetFocus
MainOrder.Show
Active today
Thanks for the compliment and helpful vote for my article. Let me know if there's anything more I can do here.
Thanks Martin.
The article is a good reference for a beginner like me. Is there a way I can add it to my knowledge base here?
On the problem in question;
I did as you said and the address is "$A$4" when I select the cell A4, which has a name of "cMakita".
The reason I wanted to try and get it to load using the named ranges of the cells rather than A4 or A16 etc was that there will be a time in the near future where lines will be inserted in the worksheet and the address will change.
The article is a good reference for a beginner like me. Is there a way I can add it to my knowledge base here?
On the problem in question;
I did as you said and the address is "$A$4" when I select the cell A4, which has a name of "cMakita".
The reason I wanted to try and get it to load using the named ranges of the cells rather than A4 or A16 etc was that there will be a time in the near future where lines will be inserted in the worksheet and the address will change.
Active today
Using named ranges should be fine.
When the code gets to the breakpoint at line 11 press F8 repeatedly to step through the code execution line by line. Does it do what you expect it to do?
When the code gets to the breakpoint at line 11 press F8 repeatedly to step through the code execution line by line. Does it do what you expect it to do?
No, The form loads no matter which cell I select.
And the frorm is not populated as I wanted nor is the rowsource updated.
And the frorm is not populated as I wanted nor is the rowsource updated.
Now it doesn't show at all no matter which cell I select
Dim a As Range
Dim b As Range
Dim c As Range
Dim d As Range
Set a = Range("cMakita")
Set b = Range("cIndesit")
Set c = Range("cMilsco")
Set d = Range("cELC")
Select Case Target.Address
Case a
Load MainOrder
MainOrder.txtCust = "MAKITA"
MainOrder.cboProd.RowSource = ("tblMakita")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case b
Load MainOrder
MainOrder.txtCust = "INDESIT"
MainOrder.cboProd.RowSource = ("tblIndesit")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case c
Load MainOrder
MainOrder.txtCust = "MILSCO"
MainOrder.cboProd.RowSource = ("tblMilsco")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case d
Load MainOrder
MainOrder.txtCust = "ELC"
MainOrder.cboProd.RowSource = ("tblElc")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case Else
'do nothing
End Select
It's a very large workbook and I have a pitiful internet connection Martin. (reminds me of dial-up)
I managed to get the form to load using the static addresses.
I managed to get the form to load using the static addresses.
Select Case Target.Address
Case ("$A$4")
Load MainOrder
MainOrder.txtCust = "MAKITA"
MainOrder.cboProd.RowSource = ("tblMakita")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case ("$A$12")
Load MainOrder
MainOrder.txtCust = "INDESIT"
MainOrder.cboProd.RowSource = ("tblIndesit")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case ("$A$18")
Load MainOrder
MainOrder.txtCust = "MILSCO"
MainOrder.cboProd.RowSource = ("tblMilsco")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case ("$A$16")
Load MainOrder
MainOrder.txtCust = "ELC"
MainOrder.cboProd.RowSource = ("tblElc")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case Else
'do nothing
End Select
Active today
Here are two ways that will work with the ranges. Note that in neither case do you need to two commented-out lines in the error handling.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrHandler
Dim a As Range
Dim b As Range
Dim c As Range
Dim d As Range
Set a = Range("cMakita")
Set b = Range("cIndesit")
Set c = Range("cMilsco")
Set d = Range("cELC")
Select Case Target.Address
Case a.Address
Load MainOrder
MainOrder.txtCust = "MAKITA"
MainOrder.cboProd.RowSource = ("tblMakita")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case b.Address
Load MainOrder
MainOrder.txtCust = "INDESIT"
MainOrder.cboProd.RowSource = ("tblIndesit")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case c.Address
Load MainOrder
MainOrder.txtCust = "MILSCO"
MainOrder.cboProd.RowSource = ("tblMilsco")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case d.Address
Load MainOrder
MainOrder.txtCust = "ELC"
MainOrder.cboProd.RowSource = ("tblElc")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case Else
'do nothing
End Select
'Exit_ErrHandler:
Exit Sub
ErrHandler:
MsgBox Err.Description
'Resume Exit_ErrHandler
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrHandler
Select Case Target.Address
Case Range("cMakita").Address
Load MainOrder
MainOrder.txtCust = "MAKITA"
MainOrder.cboProd.RowSource = ("tblMakita")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case Range("cIndesit").Address
Load MainOrder
MainOrder.txtCust = "INDESIT"
MainOrder.cboProd.RowSource = ("tblIndesit")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case Range("cMilsco").Address
Load MainOrder
MainOrder.txtCust = "MILSCO"
MainOrder.cboProd.RowSource = ("tblMilsco")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case Range("cELC").Address
Load MainOrder
MainOrder.txtCust = "ELC"
MainOrder.cboProd.RowSource = ("tblElc")
MainOrder.txtCust.Locked = True
MainOrder.cboProd.SetFocus
MainOrder.Show
Case Else
'do nothing
End Select
'Exit_ErrHandler:
Exit Sub
ErrHandler:
MsgBox Err.Description
'Resume Exit_ErrHandler
End Sub
Thanks again for your time.
It was this line that I didn't have right....
Case Range("named range").Address
Thanks for the lesson
It was this line that I didn't have right....
Case Range("named range").Address
Thanks for the lesson
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
A little background as to how I came to I design this code:
Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 9 hours left to enroll
624 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.