Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

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

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.
0
Southern_Gentleman
Asked:
Southern_Gentleman
  • 2
  • 2
1 Solution
 
FaustulusCommented:
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.
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

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?

BFN,

fp.
0
 
Southern_GentlemanAuthor Commented:
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.
Calendar.xlsm
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

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


BFN,

fp.
Q-28141375.xlsm
0
 
Southern_GentlemanAuthor Commented:
Thanks a bunch Fanpages AKA Ben
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now