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

Posted on 2013-05-28
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
  • 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 500 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

Gigs: Get Your Project Delivered by an Expert

Select from 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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

786 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