Solved

Selecting a cell

Posted on 2013-06-14

Microsoft Excel

1 solution

252 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

Comment

Question by:Stephen Byrom

[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

Join The Community

13 Comments

LVL 47

Accepted Solution

by:Martin Liss

Martin Liss earned 500 total points

ID: 392481062013-06-14

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.

LVL 47

Expert Comment

by:Martin Liss

ID: 392481332013-06-14

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

LVL 47

Expert Comment

by:Martin Liss

ID: 392481622013-06-14

Thanks for the compliment and helpful vote for my article. Let me know if there's anything more I can do here.

LVL 1

Author Comment

by:Stephen Byrom

ID: 392481742013-06-14

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.

LVL 47

Expert Comment

by:Martin Liss

ID: 392482132013-06-14

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?

LVL 1

Author Comment

by:Stephen Byrom

ID: 392482272013-06-14

No, The form loads no matter which cell I select.
And the frorm is not populated as I wanted nor is the rowsource updated.

LVL 1

Author Comment

by:Stephen Byrom

ID: 392482492013-06-14

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

LVL 47

Expert Comment

by:Martin Liss

ID: 392482552013-06-14

Can you attach your workbook and list the steps necessary to recreate your problem?

LVL 1

Author Comment

by:Stephen Byrom

ID: 392482992013-06-14

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

LVL 1

Author Closing Comment

by:Stephen Byrom

ID: 392483052013-06-14

The article alone is worth it

LVL 47

Expert Comment

by:Martin Liss

ID: 392484162013-06-14

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

LVL 1

Author Comment

by:Stephen Byrom

ID: 392484362013-06-14

Thanks again for your time.
It was this line that I didn't have right....
Case Range("named range").Address
Thanks for the lesson

LVL 47

Expert Comment

by:Martin Liss

ID: 392484392013-06-14

You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Automated Link Checker

Article by: Josh

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…

A Game of Draw Poker

Article by: Martin

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.

Building Probability Models in Excel Part 4: Discrete Random Variables

Video by: Toby

The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

How to use the IF function in Excel

Video by: Bob

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…