Solved

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

Posted on 2013-05-28
5
476 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
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.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39203879
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
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.
Calendar.xlsm
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39206455
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
ID: 39206566
Thanks a bunch Fanpages AKA Ben
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 40
Problem to With line 4 42
File not loading into PowerPivot 4 9
CUT & PASTE VALUES IN EXCEL USING MACRO -- SPECIFIC CELLS 27 18
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

920 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

15 Experts available now in Live!

Get 1:1 Help Now