Solved

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

Posted on 2013-05-28
5
469 Views
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.
0
Comment
Question by:Southern_Gentleman
  • 2
  • 2
5 Comments
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
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
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 

Author Comment

by:Southern_Gentleman
Comment Utility
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
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
Comment Utility
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
 

Author Comment

by:Southern_Gentleman
Comment Utility
Thanks a bunch Fanpages AKA Ben
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now