+ Reply to Thread
Results 1 to 19 of 19

copy range when I select a value in a combobox

  1. #1
    Forum Contributor iscar_marius's Avatar
    Join Date
    12-19-2008
    Location
    germany
    MS-Off Ver
    2000 and 2003
    Posts
    221

    copy range when I select a value in a combobox

    Please open attached file!
    First I will explain what this workbook does.
    In worksheet "sheet1" I use "change sheet" button to change the backgroud by selecting some pictures, in range S14 I have one number from the name of the background picture (using substiture function).
    By selecting one cell (with blue text) + one value from the combobox, in worksheet "sheet2" : -the selected value in column A will be deleted
    - the selected value in column B will be added
    - in column C I will see the adress of the active cell (cell with blue text)
    - in column D I will see the blue text from the active cell

    I need in column E to copy the range S14 from "sheet1" when I select a value in the combobox !
    The range S14 will have different values!
    I had tried this code (in module1 "Sub test") but it's not working how I need it to work:



    Please Login or Register  to view this content.


    If you don't understand my question I will try to reformulate !
    Attached Files Attached Files
    Last edited by VBA Noob; 03-03-2009 at 04:56 AM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy range when I select a value in a combobox

    Maybe this:

    Please Login or Register  to view this content.
    ...but that will probably only work after the sheet has at least one row of data. Are you trying to put a value in column E to match the last entry in column C? If so, maybe this:

    Please Login or Register  to view this content.
    Last edited by VBA Noob; 03-03-2009 at 04:55 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Contributor iscar_marius's Avatar
    Join Date
    12-19-2008
    Location
    germany
    MS-Off Ver
    2000 and 2003
    Posts
    221

    Re: copy range when I select a value in a combobox

    Quote Originally Posted by JBeaucaire View Post
    Maybe this:

    Range("C1").End(xlDown).Offset(0, 2).Value = Sheets("Sheet1").Range("S14").Value

    ...but that will probably only work after the sheet has at least one row of data. Are you trying to put a value in column E to match the last entry in column C? If so, maybe this:

    Range("C" & Cells(Rows.Count, 1).Row).End(xlUp).Offset(0, 2).Value = Sheets("Sheet1").Range("S14").Value
    Yes, I'm trying to put a value in column E to match the last entry in column C (or B). I added this code:
    Please Login or Register  to view this content.
    in module 1, sub test () but it doesn't work:
    Please Login or Register  to view this content.
    I don't understand why !?

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy range when I select a value in a combobox

    Me either. It works perfectly on my sheet. Here's a variation on it:
    Please Login or Register  to view this content.
    You know, check down the C column make sure there's not some value further down the sheet interfering with where you THINK the value should be appearing. Is that line causing an error?

  5. #5
    Forum Contributor iscar_marius's Avatar
    Join Date
    12-19-2008
    Location
    germany
    MS-Off Ver
    2000 and 2003
    Posts
    221

    Re: copy range when I select a value in a combobox

    Quote Originally Posted by JBeaucaire View Post
    Me either. It works perfectly on my sheet. Here's a variation on it:
    Please Login or Register  to view this content.
    You know, check down the C column make sure there's not some value further down the sheet interfering with where you THINK the value should be appearing. Is that line causing an error?
    No, it doesn't work . I don't have any error for that line.
    column C is empty, except C1 that is the header row.

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy range when I select a value in a combobox

    I repeat, it works perfectly on my sheet. We know it's not working on yours, but we can't see yours, so all we can do is test the line of code.

    If you want to post your active sheet, maybe we can see what's causing the problem.

  7. #7
    Forum Contributor iscar_marius's Avatar
    Join Date
    12-19-2008
    Location
    germany
    MS-Off Ver
    2000 and 2003
    Posts
    221

    Re: copy range when I select a value in a combobox

    Quote Originally Posted by JBeaucaire View Post
    I repeat, it works perfectly on my sheet. We know it's not working on yours, but we can't see yours, so all we can do is test the line of code.

    If you want to post your active sheet, maybe we can see what's causing the problem.
    But, I already had attached my workbook in my first post from this thread
    If you want I can attach it again!

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy range when I select a value in a combobox

    I brought up sheet2, then the VBEditor and I typed that line of code into the IMMEDIATE window and the value 73 appeared in cell E1. That means it's working.

    It appeared in C1 because there are no values C other than the headers, so it found the header row using the End(xlUp) and then went over two columns as instructed.

    If I fill in some more values in the C column, whatever the last value is, I get a "73" in column E next to it every time I enter that code on your sheet.

    So the line of code works on your sheet, too.

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy range when I select a value in a combobox

    Here's your own sheet back. Run the macro test10 which is where you were testing it. Since I added values in column C down 4 rows, the answer will appear in E4.
    Attached Files Attached Files

  10. #10
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy range when I select a value in a combobox

    OK, I examined your sub test() and watched it actually put the value in column E then take it out again later. Not sure which piece of code is doing that, so simply put this version of the code as the LAST line in your macro so nothing else comes after it and it won't get erased:
    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 03-03-2009 at 12:09 PM.

  11. #11
    Forum Contributor iscar_marius's Avatar
    Join Date
    12-19-2008
    Location
    germany
    MS-Off Ver
    2000 and 2003
    Posts
    221

    Re: copy range when I select a value in a combobox

    Quote Originally Posted by JBeaucaire View Post
    OK, I examined your sub test() and watched it actually put the value in column E then take it out again later. Not sure which piece of code is doing that, so simply put this version of the code as the LAST line in your macro so nothing else comes after it and it won't get erased:
    Please Login or Register  to view this content.
    Not working how I need it to work!

    This is how I need the macro to work:
    By selecting one cell (with blue text) + one value from the combobox, in worksheet "sheet2" :
    -the selected value in column A will be deleted
    - the selected value in column B will be added
    - in column C I will see the adress of the active cell (cell with blue text)
    - in column D I will see the blue text from the active cell
    I need in column E to copy the range S14 from "sheet1" when I select a value in the combobox !

    Please try to select values from the combobox more than one time. If I select only one value in the combobox, the code is working Ok, but if I select another value in the combobox the result is not what I need.
    I know that if I use only the code provided by you (without the other line of code) everything is ok. But I need all the code to work toghether.
    This is how it looks sheet2 if I select more than one value in the combobox:
    Please Login or Register  to view this content.
    I need sheet2 to be something like this:
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor iscar_marius's Avatar
    Join Date
    12-19-2008
    Location
    germany
    MS-Off Ver
    2000 and 2003
    Posts
    221

    Re: copy range when I select a value in a combobox

    I forgot to say thanks for all your effort!

  13. #13
    Forum Contributor iscar_marius's Avatar
    Join Date
    12-19-2008
    Location
    germany
    MS-Off Ver
    2000 and 2003
    Posts
    221

    Re: copy range when I select a value in a combobox

    Is not mandatory to use the code from the attached workbook, you can give me an alternative for that code
    I don't care what code I use, I just need it to work.

  14. #14
    Forum Contributor iscar_marius's Avatar
    Join Date
    12-19-2008
    Location
    germany
    MS-Off Ver
    2000 and 2003
    Posts
    221

    Re: copy range when I select a value in a combobox

    I really need to make this work. Can anyone help me ?

  15. #15
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy range when I select a value in a combobox

    Sorry, I gave up trying to figure your code out. Hope someone else has better luck than I did.

  16. #16
    Forum Contributor iscar_marius's Avatar
    Join Date
    12-19-2008
    Location
    germany
    MS-Off Ver
    2000 and 2003
    Posts
    221

    Re: copy range when I select a value in a combobox

    Quote Originally Posted by JBeaucaire View Post
    Sorry, I gave up trying to figure your code out. Hope someone else has better luck than I did.
    Thank you for your time.

    I added some comments in module1 from the attached file (to explain better what this code do):
    Please Login or Register  to view this content.

    I need in column E to copy the range S14 from "sheet1" when I select a value in the combobox !
    The range S14 will have different values!
    Attached Files Attached Files

  17. #17
    Forum Contributor iscar_marius's Avatar
    Join Date
    12-19-2008
    Location
    germany
    MS-Off Ver
    2000 and 2003
    Posts
    221

    Re: copy range when I select a value in a combobox

    Is not possible to do this ?

  18. #18
    Forum Contributor iscar_marius's Avatar
    Join Date
    12-19-2008
    Location
    germany
    MS-Off Ver
    2000 and 2003
    Posts
    221

    Re: copy range when I select a value in a combobox

    I am still trying to make this work without success.

  19. #19
    Forum Contributor iscar_marius's Avatar
    Join Date
    12-19-2008
    Location
    germany
    MS-Off Ver
    2000 and 2003
    Posts
    221

    Re: copy range when I select a value in a combobox

    after all this time, I still try to make this work without success
    I would appreciate any help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1