Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Move specific cell data to another worksheet and range name in MS Excel

Posted on 2013-05-28
Medium Priority
Last Modified: 2013-05-29
I have a row of data with hidden columns. I want to create VBA code tied to a check box event that will move data in specific cells in the same row the check box was select to a specfic range name in a different worksheet in the same workbook.

I basicly have a Date and when the checkbox is checked to True, a button will take specific data and move it to an excel  calendar. For each day in the calendar, I gave it a range name of the coinciding date. The information that is moved to the range name comes from a date column and the check box moves the data from the row to the range name.

Column A has the checkboxes (One for each row) Column B has the dates, Column C-F has the information that i want to be moved to the excel calendar with the date range names in another worksheet. Hopefully this is enough information.
Question by:Southern_Gentleman
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
  • 2
  • 2
LVL 14

Expert Comment

ID: 39203703
A workbook will be needed in order to test and debug any code one might write. So, unless you wish to do that yourself I have to destroy your hope: your information is not sufficient.
Basically, if your calendar has dates in it, range names representing those dates would be superfluous. Note that you can format a cell to display only the day of a date. For example, you could enter a date like May 29, 2013 in a cell and let the cell show only 29. Perhaps, for your purposes you might wish to write to the cell next to the one with the date. If there is some pattern in the relationship between date cell and target cell that would still be easier than setting up named ranges for every day, and no difference for the programmer.
The real problem in your project is in your check boxes. I can describe them in two words, "Not good".
You see, check boxes aren't placed on the worksheet relative to cells. In fact, Excel doesn't place them on the worksheet at all. Instead, they are on a different level which you see superimposed over the worksheet. Now, you could place CheckBox49 next to row 49 and establish a relationship between the two levels in that way. The problem is in that you don't see the CheckBox's name unless you look up the properties. Management of check box names becomes a major task, and quickly adding a row along with its check box by simple copy/paste just doesn't work.
The better way would be to set the check mark in a cell using a validation drop-down. This is how you go about that:-
1. Set up validation for the cells where you want the check mark. Allow only the letter P (capital P) or nothing (blank cell). If you prefer the check mark to be in a box, use the letter R.
2. Format the Font for the cell with validation to use Wingdings2. This will make the check mark appear in place of the letter you type or select.
You may wish to increase the size and make it bold or even change the font color, or you may wish to add conditional formatting so that the cell's background gets painted whenever you set the mark.
If you can make such a change the programming shouldn't be too difficult once you provide a workbook to put it in.
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39203879

I basicly have a Date and when the checkbox is checked to True, a button will take specific data and move it to an excel  calendar. For each day in the calendar, I gave it a range name of the coinciding date. The information that is moved to the range name comes from a date column and the check box moves the data from the row to the range name.

Have you considered what will happen if a previously selected (checked/ticked) checkbox is unchecked & then checked again?

Will the "checked" row of data overwrite what is stored in the "Calendar" data?  What if manual changes have been applied to the "Calendar" data?  Should the "checked" row data overwrite, or append to, the "Calendar" data in this case?



Author Comment

ID: 39206393
Thanks for the information guys. I've decided to go with a different approach. I found a nice calendar from one of the questions on this website but I'm having trouble with three distinct issues. I guess I'll post the first one here and maybe it can get rid of the compile errors that are appearing. First, when you go to the 'Project-Calendar New' tab i'm trying to figure out how to do a simple concatenate of what's already showing on the calendar and from the 'Details' tab the "Business Name"+"QTY"+"Estimated Ship Date". This will have a space in between each concatenated item.  The error that comes up is when a person clicks on an item on a specific date i'm running into compile error which I can't seem to figure out.

Thank you again. I'll post my second question which shouldn't effect this question. Here is the attachment.
LVL 35

Accepted Solution

[ fanpages ] earned 2000 total points
ID: 39206455

I have resolved the issue(s) you were having.

Please see the attached workbook.

However, please note my comment within the Worksheet_SelectionChange() event of the [Project-Calendar New] worksheet:

If lngRow > 0& Then
   Str1 = Worksheets("Detail").Cells(lngRow + 1&, 2)
   Str2 = Worksheets("Detail").Cells(lngRow + 1&, 3)
   Str3 = Worksheets("Detail").Cells(lngRow + 1&, 4)
   Str4 = Worksheets("Detail").Cells(lngRow + 1&, 7)
   Str5 = Worksheets("Detail").Cells(lngRow + 1&, 9)
   Str6 = Worksheets("Detail").Cells(lngRow + 1&, 10)
   Str7 = Worksheets("Detail").Cells(lngRow + 1&, 6)    ' <- Note: Column 6 is QTY, not Order Date
   Str8 = Worksheets("Detail").Cells(lngRow + 1&, 15)
   Str9 = Worksheets("Detail").Cells(lngRow + 1&, 11)
   Str10 = Worksheets("Detail").Cells(lngRow + 1&, 8)
   Str11 = Worksheets("Detail").Cells(lngRow + 1&, 5)
   Str12 = Worksheets("Detail").Cells(lngRow + 1&, 1)
   Str13 = Worksheets("Detail").Cells(lngRow + 1&, 6)
   Str14 = Worksheets("Detail").Cells(lngRow + 1&, 12)
   Str15 = Worksheets("Detail").Cells(lngRow + 1&, 13)
   Str16 = Worksheets("Detail").Cells(lngRow + 1&, 14)
End If ' If lngRow > 0& Then

Open in new window



Author Comment

ID: 39206566
Thanks a bunch Fanpages AKA Ben

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

670 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