CheckBox Code to copy & paste range

Mobys of VBA,
I 'm trying to write what I assumed was a simple copy & paste procedur
using a checkbox value as the trigger. I have tried numerous variations
but to no avail. I get no error messages, I also get no desired results
except that everytime I run the code it does unprotect and protect th
worksheet and it always clears the contents of rng2.
The two named ranges (rng1 & rng2) are unprotected and the same siz
and configuration (i.e. A20:A22, A43:A45 respectively) , but they ar
on different sheets. Thanks for any help
Here is my best code to date:

Option Explicit
Private Sub ChkboxRFP_Click()
Dim i As Boolean
Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Sheets("Name Sheet").Range("RFPMsgSource")
Set rng2 = ActiveSheet.Range("RFPMsgDestination")

i = CheckBox1.Value
ActiveSheet.Unprotect ("geekk")

If i = True Then
rng2.ClearContents 'Clear old text
rng2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
ElseIf i = False Then
End If

ActiveSheet.Protect ("geekk")
End Su


Casey's Profile:
View this thread:

10/13/2005 4:32:47 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 20


Similar Artilces:

Making attribute a checkbox
How do you make an attribute a checkbox -- donn Hi, In Customization, open the entity. Than open click on Forms and Views and open the Form. Double click on bit type of field. In new window, the second tab will be formatting. You will have option in bottom, "Control Formatting" here you can choose the desrired format. -- PLEASE do click on Yes or No button if this post was helpful or not for our feedback. uMar Khan Email for direct contact: imumar at gmail dot com "Donald" wrote: > How do you make an attribute a checkbox > -- > donn Create a bit field an...

Copy Set of records between 2 subforms
Hi, my database is for my clients purchases (from different suppliers). The main Purchase form includes information as client name, supplier, date etc. The same form also includes 2 subforms - first is the actual order details (product, price,qty) and the second is the arrival order details (Product, Price,qty). Each Purchase can have several products. Each subform is based of a different table, both linked (one-to-many) to the mainform. This is done since the arrived order maybe different from the actual order, and I have to keep both sets of records. So, I would like to use the "...

Coding issue with Excel 2003
Hi all Gurus, below is code to transfer data from sheet 1 and 2 into an external sheet. I want to have a button so that when pressed it transfers a range of cells from sheet 1 and 2 and places it in the first available row in the external sheet, all the information needs to go into sheet 1 of the external sheet. Please help, i would greatly appreciate it. Neil. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Dim wb1 As Workbook, wb2 As Workbook Application.DisplayAlerts = False Application.ScreenUpdating = False Set wb1 = Acti...

Dialogsheet Checkbox to Select All Checkboxes
I am using a dialogsheet to get user input. The dialogsheet is created at runtime. The amount of checkboxes on the dialogsheet varies based on how many tabs are hidden. I would like to have a checkbox on the dialogsheet that allows users to "select all" checkboxes. Code Example: Dim SheetCount As Integer Dim CurrentSheet As Worksheet Dim PrintDlg As DialogSheet Dim cb As CheckBox Set CurrentSheet = ActiveSheet Set PrintDlg = ActiveWorkbook.DialogSheets.Add 'some code used to loop and build checkboxes not shown here If SheetCount <> 0 Then If ...

Copy data into a table (but not the formula)
Hi First of all I apologise if this has been asked before. What I want to know is it possible, and if so how does one do it, to d the following ? Copy data from a cell into another cell in a table but without having formula in the destination cell , e.g Say $b1 contains the name Fred and I want to place Fred into the $c cell but I don't want there to be a formula in $c5 asking for it tha will update the contents of $c5, if I change the value held in $b5, fo instance, I put Fred in $b1 and Fred appears in $c5, but then I pu another name, say Bert into $b1 and it goes to $c6, but the co...

Re: Using A Macro/VBA code to re-set formulas
Trevor when you recommended using : Sub CopyFormulae() Application.ScreenUpdating = False Range("C14").Formula = "=SUM(A14:B14)" ' <<< Change the Formula here Range("C14").AutoFill Range("C14:C40") Application.ScreenUpdating = True End Sub when I tried putting in my formula i.e. =IF(ISERROR(VLOOKUP(Pick2,Data!$B$2:$C$1067,2,FALSE)), ",(VLOOKUP(Pick2,Data!$B$2:$C$1067,2,FALSE))) It displayed an error message due to the " " in the middle of m formula ! Is there syntax to avoid this Ro -- Message posted from http://w...

range where clause??
Hi, I am using the range where clause in my code. I just read that this works only with the SQL Server & not with other databases line Pervasive SQL & C-tree. If I use the range start & range end clauses would the code work for all the databases? I have completed the coding. What would you suggest at this point? Is it better to recode everything or is there any other solution? Thanks & Regards, Sup Hello sup, Since MBS that other databases arn't supported any longer, why would you need to build code to support it? Kind Regards Eddie Fourie MBS Specialist s> Hi,...

checkbox with relative reference?
I have a checkbox in E5 controlling E5. If I copy it to K11, I want it to control K11. Now, how do I give it this kind of relative reference? Thanks, Sven Sven, I take it you're going to have to do this more than a few times. I suggest you create the checkbox objects in code rather than using the Excel Control Toolbox. Add the checkboxes in the workbook open event and then you can set the attributes of each using variables. Ross "Sven Berg" <> wrote in message >I have a check...

How to paste autoshape with changed properties in publisher 2007?
I tried to paste a rectangle in line with the text, and the publisher was changing the pasted autoshape into a defult (which is teh exact position). How do I paste the autoshape with my own properties? Select the autoshape, from Format menu click AutoShape, click the Layout tab and change the Object Position to Inline. If you are copying a rectangle that is already inline and wondering why it isn't inline when you paste it, it's because its inline-ness is a property of the text it is in. This can be confirmed by selecting the autoshape and at least one character of text on eit...

Checkbox checked then show more checkboxes
I have added checkboxes from Control Toolbox and they seem to work fine. I just want to make one of them lets say checkbox3 to show more checkboxes when checkbox3 is marked. How do I do that? I'd add them all to exactly where I wanted them. But then have the "master" checkbox just unhide/hide the others. Option Explicit Private Sub CheckBox3_Click() Dim ShouldBeVisible As Boolean ShouldBeVisible = CBool(Me.CheckBox3.Value = True) Me.CheckBox1.Visible = ShouldBeVisible Me.CheckBox2.Visible = ShouldBeVisible Me.CheckBox4.Visible = ShouldBeVisible ...

Colour code messages only to me
Hi I am running Outlook XP. There is a rule that can be set to colour code emails that are sent only to me, but what it actually does is colour code emails that only have me in the To: line of the email. If an email is sent to me and CC's in someone else, the rule treats this as a mail sent only to me. How do I set the rule so that it colour codes a message sent solely to me? Thanks in advance ...

How to copy a cell to another sheet having more than 255 characte.
It is copying only first 255 character.. How to resolve this problem to copy entire cell content ...

Macro error, range object need data?
Hi all How can I improve my macro which has a macro error like below: "Macro error The method can't be used in the applied context. Specifically, some Range object methods require that the range contain data. If the range does not contain data, the method fails." How can I modify range object which has some empty data in the range? Thanks Daniel Hi, Does the range you are referencing contain data? Empty cells or those containing #N/A can cause problems when using the charting section of the object model. Cheers Andy Daniel wrote: > Hi all > How can I improve ...

Code working in 2K3, but not 2K7
I have a form with one unbound combo box. The On Open Event is set as follows: Private Sub Form_Open(Cancel As Integer) Me.Text0.SetFocus Me.Text0.Dropdown End Sub Works eveytime in Access 2K3 , but in 2K7 the form opens and the cursor is blinking in the combo box. Any idea on why the combo box will not drop down and display records? I have tried changing Overlapping Windows and Tabbed Documents settings, but no difference. If I remove the code entirely, the form opens and the combo box has the focus because it is the only object available. Any help appreciated. Try mov...

automatic row copying
Hello; I have a workbook with several worksheets representing months in the last 18 months, each month with its own worksheet. It's for tracking jobs, each separate job listed in its own row with information about that particular job, including job number, street address, cost of the repair, etc. Is it possible in Excel to have a full row of information automatically copied to one specific sheet in that same workbook if the repair value is greater than a certain amount? So, all jobs from all months that have a repair value of over 5,000, for example, would automatically be transfe...

Exit code question
When running an MFC VC6++ app in debug, after the program terminates I see the following information in the debug window: The thread 0x75C has exited with code 2 (0x2). The thread 0xAF8 has exited with code 2 (0x2). The thread 0x9D0 has exited with code 2 (0x2). What does code 2 mean? Thanks, Jack it means a 2 was supplied as the argument for exitthread(). to find out more you have to figure out what those threads are and under what conditions they exit with code 2. "Microsoft" <> wrote in message news:upr2BEuBFHA.3120@TK2MSFTNGP12.phx.gbl... > When r...

How do I add checkboxes into cells in an Excel spreadsheet? Hi, Jay, First make sure you can see a checkbox by going to menu, view>toolbars> select either formatting or control box (they give you two different types of controls) then simply click on the checkbox, go to your location and click'ndrag to size it. YOu can then right click on it to set properties, assign a macro, etc. jeff >-----Original Message----- >How do I add checkboxes into cells in an Excel spreadsheet? >. > ...

Coding "Category (X) Axis"...
I'm running Access-to-Excel automation, and using Auto_Open in Excel to see things like.. ActiveChart.ChartTitle.Text = " The Week" & TheWk Works fine. I want to "fill a label on the Chart" with a concatenation. Can I... ActiveChart."xxx???".Text = "ABC " & an Example. What would be the ""xxx???" for a "label" such as the "Category (x) Axis" TIA - Bob Hi, Macro recorder gave the majority of the code. With ActiveChart .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(x...

Get the html source code from IWebBrowser2
Hello I want to get the full html source code from the IWebBrowser2 control. I tried this code, but it only return the html code in the body tag. CComQIPtr<MSHTML::IHTMLDocument2> pDoc=pDisp; MSHTML::IHTMLElementPtr pElement; pDoc->get_body(&pElement); CComBSTR bstra; pElement->get_outerHTML(&bstra); thanks in advance I think you need to use get_all instead of get_body. That will give you a collection of all elements in the document. --- Ajay ...

Error Code 8007007e
I've been getting this for over a month. Since then, i've been looking all over to try to find a fix.. and have been unable to. Even with the things listed on this site, I haven't been able to fix it.. I run off of Windows 7. Help? -- Masen Loy Posted via Nothing? /: -- Masen Loy Posted via "Masen Loy" <> wrote in message > > I've been getting this for over a month. Since t...

Copying Checkbox Values into a different worksheet
Hi there, I'm trying to copy the value of several checkboxes to another worksheet. This works basically all fine by using the code below Sub EvaluateCB() ActiveWorkbook.Worksheets("Survey").Select Range("A1").Select d = Worksheets("Sheet1").CheckBox1.Value Selection = d End Sub But, as there are about 40 checkboxes I wonder whether there is a way to do this all automatically, i.e. by some for loop going through all the checkboxes. As I am a complete rookie in Excel I appreciate any hint / suggestions. Many thanks, Thiery You ...

a problem when using a listctrl with checkbox
Hi all, I created a listctrl with checkbox (LVS_EX_CHECKBOXES) and found the checkbox could be set to checked/unchecked even by right-clicking. why? what special handling is needed for prohibiting it? thanks. bq You need to override the right click method in the control so make an extension of the listctrl class: In class wizard make a new class ListCtrlEx with base class CListCtrl. Then choose the dlg class and make a member variable for the list box say m_list and make it a control of type ListCtrlEx Then go to the message maps tab and choose class ListCtrlEx. Add a function for WM_R...

Hard-coding a date
I'm trying to hard-code a date onto a report. I know I have to use a text box and then use the builder for the control source. I also know I have to use the format and date functions, but I can't figure out the correct formatting for the expression. This is what I have been typing in: format date() "MMMM D, YYYY" but it never works. Erin - To format today's date, use this as the control source of your field: =format(date(), "MMMM D, YYYY") -- Daryl S "ErinCullen" wrote: > I'm trying to hard-code a date onto a re...

Clean Up Code
Hi All... I have code that contains the following: ActiveCell.FormulaR1C1 = "USB" Range("A2").Select ActiveCell.FormulaR1C1 = "04165" Range("A3").Select ActiveCell.FormulaR1C1 = "14709" Range("A4").Select ActiveCell.FormulaR1C1 = "14716" Range("A5").Select ActiveCell.FormulaR1C1 = "24704" Range("A6").Select ActiveCell.FormulaR1C1 = "44705" Range("A7").Select ActiveCell.FormulaR1C1 = "44710" and it goes on to ent...

Require selection from combo on checkbox?
Hi - is it possible to set a form to require a selection from a combo if a check box is set to true? I've tried using the validation rule set to the check box name, but it doesn't work... Steve Since we're not there, we don't have any way to know HOW you "tried using the validation rule ..." If this were mine, I'd add a validation check in the form's BeforeUpdate event that ensured that there was a selection made in the combobox if the checkbox were true (and "Cancel"ed the update if there wasn't). By the way, you can make it easier f...