Solved

Selecting a cell

Posted on 2013-06-14
13
213 Views
Last Modified: 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
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

Open in new window

0
Comment
Question by:Stephen Byrom
  • 7
  • 6
13 Comments
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39248106
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39248133
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39248162
Thanks for the compliment and helpful vote for my article. Let me know if there's anything more I can do here.
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39248174
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39248213
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?
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39248227
No, The form loads no matter which cell I select.
And the frorm is not populated as I wanted nor is the rowsource updated.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39248249
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

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39248255
Can you attach your workbook and list the steps necessary to recreate your problem?
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39248299
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.
    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

Open in new window

Thanks for your time
0
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 39248305
The article alone is worth it
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39248416
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

Open in new window


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

Open in new window

0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39248436
Thanks again for your time.
It was this line that I didn't have right....
        Case Range("named range").Address
Thanks for the lesson
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39248439
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now