Insert A Value Into The Active Cell In This Workbook Based On The Value Of A Checkbox In The Other Spreadsheet
I have 2 workbooks open, one called "Temp" and the other called "CRM 2"
My active workbook is "Temp". I am trying to insert a value into the active cell in this workbook, based upon the value of a checkbox in the other spreadsheet. Here's my
If Windows("CRM 2.xls").SelectedSheets.CheckBox6.Value = True Then Selection.Value = "By Phone"
I've tried variations on this such as:
If Windows("CRM 2.xls").ActiveSheet.CheckBox6.Value = True Then Selection.Value = "By Phone"
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Insert Cell Comments Based On Value In Another Workbook
I need a macro that will do the following scenarios. 1. I have two workbooks. a. The first contains a sheet with " Names" listed in column A. b. The 2nd contains a sheet with "Names" listed similar to first workbook. The only difference is that the 2nd workbook contains 2 other columns, "Age" in B and "Address" in C. 2. Only the 1st workbook is opened. 3. When I click a name in the 1st workbook, it will look for a match with that of the 2nd workbook. a. If there's a match, it will copy the corresponding "Age" and "Address" and display them as comment to that name in the 1st workbook. b. Else, it will display a comment that no record was found.
View Replies!
View Related
Copy Of Active Workbook Path Stamped Onto Spreadsheet With Date And Time / Edit Check
1) I need to add an edit check 2) have a copy of Active Workbook Path stamped onto spreadsheet with date and time to create a visual record of where the file has been saved (described after the code below). 1) I need to verify that two cells (S7 and S9) are not blank before running my code below (=IF(OR(S7<>"",S9<>""),RUN CODE,"You must select your Provider or Division before you can save this document")). - If both of these cells are blank a message box should notify the user that they must select the provider and/or division before they can continue with the save. - If one or more of these cells are not blank the code below should run.
View Replies!
View Related
Create New Workbook Based On Checkbox Selections
Could anyone please help me frame a vb code for the below explanation? I have a sheet where in some terms are provided. Users have to open this sheet and check its description. After going through all the terms, they have to select the required terms using a checkbox given beside these terms. After checking the reqd. boxes, they would click on 'Submit' at the end of the sheet. Once Submit is clicked, a new excel workbook should open up with the selected terms as various column headers.
View Replies!
View Related
Insert Rows At The Active Cell
I want to be able to allow a user to insert a number of rows (they would specify the number) into a worksheet, at the active cell location. I also need the same number of rows to be inserted into the same worksheet (but much lower down) The location of which is to be governed by the location of the original inserted rows. Having created the new rows I need all the formulas from the row ABOVE the active cell to be copied into all the newly created rows. example If active cell is A5 I want user to be able to run a macro which asks them how many rows they wish to insert. (Assume the user asks for 7). The macro then inserts that number of rows, (starting at row 5). And additionally adds 7 rows at a location further down the worksheet (say row 105). The formulas from the row above the active cell are then copied into all the newly created rows. 2nd example If the active cell was A9 then the macro would add 7 rows, starting at row 9 and additionally add 7 rows starting at row 109. Then formulas added.
View Replies!
View Related
Open Workbook, Find Sheet That Contains Cell Value From Active Workbook
I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason... 'Dim officen As Integer 'Dim thiswb As Workbook officen = Range("A2").Value Set thiswb = ActiveWorkbook ' Open the Active Info file Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx" ' Dim sourcewb As Workbook Set sourcewb = Workbooks.Open"Active 20080616.xls" Sheets("officen").Select RowCount = ActiveSheet.UsedRange.Rows.Count Range("B2").Select.............................
View Replies!
View Related
Macro: Create Directory Based On Active Workbook And Save To It
Read “My Documents” Path And Use Result Problem: Note: Typical user OS will be Windows XP Pro / Win 2K Excel version : 97 / 2002 / 2003 1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers. 2. Per customer request, VBA application needs to save extracted files for future use. 3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”. 4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following: Dim filepath As String filepath = ThisWorkbook.Path As an example – this code would produce a string definition of “filepath” – such as the following:............................
View Replies!
View Related
Insert Picture & Auto Size To Active Cell Dimmensions
What I am trying to do is to give the user the ability to insert a photo and have it autosize into a range of cells--so far I can only get it to work with a single cell and a predefined file. Can this be refined so that rng references a group of cells and pic somehow lets the user input a file name or pick its name and location? Sub test() On Error Resume Next Set pic = ActiveSheet.Pictures.Insert("C: ange.gif") On Error Goto 0 If Not pic Is Nothing Then 'Found it!' Set rng = ActiveCell With pic .Height = rng.Height .Width = rng.Width .Left = rng.Left .Top = rng.Top End With End If End Sub
View Replies!
View Related
Checkbox To Change Cell Value Based On Other Checkboxes
I currently have a spreadsheet with a userform that has multiple checkboxes. I want these checkboxes to change the value of cells depending on whether or not they are checked. 1 for not checked, 2 for checked. The column of the named range the cells are in is constant for each checkbox (though it is different for each checkbox and some checkboxes will need to change the value of a cell in multiple columns). The row changes depending on the value of a combobox on the userform. I have sorted out a code that works, but it seems very long, slow, and inefficient. I'm hopping someone can show me a better way to do it. Private Sub CmbFinish_Click() Dim rClSkills As Range Dim rClLookup As Range Dim var1 As Integer Dim iDecision As Integer Set rClSkills = Range("TblClSkills") Set rClLookup = Range("ClassLookup") iDecision = MsgBox("Are you sure you wish to change Skills?", vbYesNo, "Continue?") If iDecision = vbYes Then var1 = Application.WorksheetFunction.Match(cboClass.Value, rClLookup, 0) If CboAppraise.Value = True Then.............................
View Replies!
View Related
Return Value To Cell Based On CheckBox TRUE/FALSE
I would like to create a qestionairre with checkboxes used to answer the question "Do you need____" (Check if yes). On the following workbook I would like a list of all items needed, and a list of items not needed on yet another workbook. Is there a way to create these lists without having blanks for values that are not true? (and because Im sure there is HOW?)
View Replies!
View Related
Tally Cell Automatically Based On CheckBox State
In my spreadsheet the user has the option to put in a numerical value into different cells, which will update (add to) another. So if we have an initail value of 20 in "I10" and the user puts in 20 in "W12", then I10 will display 30. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("W12:X24")) Is Nothing Then Select Case Target Case Is = Range("W12") Logbook.Show Range("I10").Value = CInt(Range("I10").Value) + CInt(Target.Value) Case Is = Range("X12") Logbook.Show Range("J10").Value = CInt(Range("J10").Value) + CInt(Target.Value) Case Is = Range("W13") Logbook.Show.................
View Replies!
View Related
Workbook Save, Make Sure Active Cell Is A1
When saving a file that has 5 sheets that extend to the right of the users screen, sometimes the left part of the report is left somewhat hidden because the last person to save it was using the part to the far right. Is there a way to move all the active cell to A1 and make sure rows A:D or so are showing?
View Replies!
View Related
Link A Dot Checkbox To A Particular Column Of Another Spreadsheet
I am doing a project and met with a problem with the excel spreadsheet. will keep my questions short and sweet for easy reading. i have 2 excel spreadsheet which i need to link up. one has an option for you to choose your nationality eg. american /chinese the option is done via a "dot" checkbox once "american" is checked, and the name is entered at a column below it. the name will be keyed into the 2nd excel spreadsheet under the "american column" and if its checked "chinese", the name will be entered under "chinese" column which is also in the same spreadsheet. >>i understand that once i keyed in more entries. i need to create a For...loop whereby the system will do a run-through to check for a empty slot, insert another row and place the data in the rows. >>how do i link up and make sure that the computer will know when to put under the "american" column and when to put under the "chinese" column.
View Replies!
View Related
Checkbox To Delete Or Insert Rows
I have a worksheet that has been created to essentially request information needed to determine a demo system configuration. Within this worksheet there are a series of checkboxes that if checked, will need to have the form extended (i.e. add additional questions to clarify say a model or manufacturer). For example, if the Yes checkbox is checked next to a question I want a macro to copy some rows from a hidden worksheet within the same workbook to the bottom of the non-hidden worksheet. If the No checkbox is clicked, nothing should happen. Furthermore, if a user accidently click Yes but didn't mean to I want the added rows to be removed. This is further complicated by the fact that there are 2 or 3 questions that have check boxes that can add additional questions.
View Replies!
View Related
Insert Row On Sheet & Move Active Cell Row To It
I would like to create a macro that could archive entries from one sheet and insert them in another. I created one but the problem is that the entry has to be the same row each time. Example: Sheet 1 – is current jobs and sheet 2 is old jobs. My macro moves an entry from Row A-5 of Sheet 1 and moves it to the top of Sheet 2. I would like to be able to scroll through each entry select it and have it moved to the top of the Old Jobs sheet.
View Replies!
View Related
Insert Row At Active Cell With Formula From Fixed Row
I want to insert a new row that contains the formulas of a fixed row (1:1). The inserted row is changeable and is determined by whichever is the current active cell. Eg: Active cell is something random like E16 I want to add a new row but don't want a blank row - rather want a row that contains the properties of 1:1
View Replies!
View Related
Select A Range Based On Active Cell Location?
I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result. For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z). The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).
View Replies!
View Related
Merge Range Based On Active Cell Row
I am working on a macros that creates a new row for every data entry. Below is the macros that I have. In the new row, I want for the cells in columns F through O to merge right after creating the row. How do I go about this? If Sigma = 0 Then Selection.EntireRow.Insert ' New row for new entries ActiveCell.Value = "NONE" ActiveCell.Offset(1, 0).Select End If
View Replies!
View Related
Find Text Based On Active Cell Contents
I have a workbook with 2 worksheets, A and B. Sheet A contains source data and sheet B a pivot table of this data. I want my user to click on a cell within the pivot table,click a button that runs a macro to find the instance of this value within Sheet A. I did record a macro but it did not work.
View Replies!
View Related
Copy Data Based On Cell Entry To Different Spreadsheet
i am trying to find a way to automatically copy information from a worksheet on my computer([list.xls]-List of accounts) to a worksheet on a shared folder([summary.xls]- accounts that have paid) example:[list.xls]Sheet1!A:A has account numbers, C:C has account balance and D:D has notes on account. If D:D is "paid", copy acc# to [\foldersummary.xls]Summary!A1, balance to B1 and notes to D1. If D:D is "payment pending" do same as above but in row 2 and so on.
View Replies!
View Related
Insert .jpg Based On Cell Value
I have some code that changes the colour of a cell based on the value of that cell, not sure how useful this is as I could just simply use conditional formatting. I have a pivot table that is constantly changing values, the column is a result of a number of days...not really relevant.... however If the cell contains a value of greater then 7 I want to insert a .JPG of a lil stop sign, if less then 7 then a little green go sign will be insertedThis is what I have so far..just changes cell color. Sub DoOnSelection() Dim oCell As Range For Each oCell In Selection If oCell.Value > 7 Then oCell.Interior.ColorIndex = 3 End If Next End Sub
View Replies!
View Related
Insert A Comment Based On The Value Of A Cell
I have data validation and conditional formatting set for my sheets. However, I cannot figure out something. I want a "comment" box (just as if I would click on "add comment") to pop up automatically if the user enters a certain value in a cell. For example, if cell C4="1" then no further action is required. If cell C4=anything other than "1", then the user would be required to enter a comment. I would also like the comment to autopopulate with the users login id. I will attach the book I am working on for reference, but I do not have any code written for this as I could not figure out where to start.
View Replies!
View Related
Insert Rows Based On A Value In A Cell
I need to create a macro that will insert exact number of rows based on the value in certain cell. e.g. value in cell F2 of sheet1 is 3...so I need to insert 3 rows down from cell F9 in sheet2 copying data in cells D9 and E9 to inserted Cells / rows.
View Replies!
View Related
Insert Rows Based On Cell Value
I need to insert a row based the value of in column L. For example, beginning at L10 and down, are values. These are subtotals that are dynamic (as in they move based on how much data is inputted to the worksheet monthly.) I need to insert a row above the value. So if L22 has (2,961.25) in it, I would like to it down and insert a blank row. So on down the sheet.
View Replies!
View Related
Insert 2 Lines Under Line 6 In The Active Sheet
I have to do a large amount of routine work with a large number of sheets. I have tried to record some macros to do the job. First of all I would like to insert 2 lines under line 6 in the active sheet. I then need to drag the formulas found in line 6 down over the two new lines. Thirdly i would like to change the text string "xxx" in the formulas found in line 7 to "yyy"
View Replies!
View Related
Insert Next Row Based On Previous Cell Value
My current code works, but there's got to be a shorter version to insert rows based on a cell value. Currently my code works on a series of If statements. If the value in the current cell is "2" then goto the next row and insert one line. If the value is "3" then go to the next row, insert, next row insert etc. I'm currently written up to a value of 10, but the coding is getting longer and longer. Anybody got a shorter loop that I could use.
View Replies!
View Related
Insert Rows Based On Cell Time Value
I have three columns - "Start Time", "End Time", and "Elapsed Time". Elapsed time is just end time - start time. What I would like to do is insert rows based on elapsed time. For instance if start time is 6:00 and end time is 6:10, then elapsed time is 0:10. In this case I would like to insert 10 rows below. Basically I want to insert 1 line per minute (based on elapsed time).
View Replies!
View Related
Insert Formula Based On Cell Entry Using Vba
i'm sure this can be done but i cant get an angle on the method. i want to use VBA to put a formula into cell G3 based on the users entry in cell D3 so, for example the user enters M in cell D3 and the VBA code puts the formula "if D3="M",A3,0"into cell G3 i know i could use a formula in the cell but i want it to work with multiple entries so i figure VBA is the way to go.
View Replies!
View Related
Insert Row Macro Based On Cell Criteria
I'm trying to create a macro that takes data from one row and inserts it into a new row. Attached is a workbook with a before and after example of what I'm trying to do. Each row has a security transaction that includes principal cash and interest. The data needs to be formatted so that each transaction has one row for principal and one row for interest. Principal is identified by the tran code PAYDOWN in column C. Interest is identified by the tran code INT. However, the raw data generated includes both interest and principal under the tran code PAYDOWN. There could be 100 or 1000 transactions generated based on the day.
View Replies!
View Related
Vba To Copy And Insert Row Based On Cell Value
vba to copy and insert row based on cell value I have a spreadsheet with serveral thousand lines. I need to add row(s) and fill-in values based on a value in anothe cell; for example, Based on the value in Column A, a row(s) needs to be added below the row and a count (value) to B needs to be inserted into the cell in Column B. This step needs to be repeated for all rows to the end of the sheet. I would want to copy and insert the entire row. LABELAB110 2 1
View Replies!
View Related
Insert Number Of Rows Based On Cell Values?
Column B contains geographical Areas. Column C contains a list of business departments. North Env North Env North Ops North Sales North Sales North Sales South Env South Maint South Ops South Sales South Sales South Sales Etc. The values and number of these departments will vary. I want to insert lines to sum the totals at the bottom of each geographic area based on the number of different departments. So, for this example, for the North three lines would be inserted. For the South, 4 lines would be inserted.
View Replies!
View Related
Insert Rows & Headings Based On Cell Value
Inserting Rows and Headings. Is it possible to automatically insert Rows and Headings based on the Cell value of a particular column ? For example column B consists of a field called, 'Assigned Group'. Column A consists of a field called, 'Fault description'. Column A needs to have a heading depending on the value of Column B. One row also needs to be inserted above the heading.
View Replies!
View Related
Import CSV File Using Cell As File Name & Active Workbook Path
I am trying to import some csv files so I can combine them, but am having probs with the filename and location. Sub test() Dim wsName As String wsName = ActiveCell Sheets("Data").Select With ActiveSheet.UsedRange LastRow = .SpecialCells(11).Row End With With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow)) .Name = wsName .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells.....................
View Replies!
View Related
Copy Worksheets From Active Workbook To A New Workbook
I am trying to code a Macro so that i can take all the worsheets and save them as individual Workbooks. I wrote a macro that appeared to work, but, after it saves the first sheet as a workbook, i get a debug error. MS VB Script error: Runtime error '9': Subscript out of range Any advise would be greatly appreciated. Thank you Code is below.. Sub saveall() ' ' For Each ws In ActiveWorkbook.Worksheets ThisFN = "C:Documents and SettingsUserDesktop" & ws.Name & ".xls" I = I + 1 Sheets(I).Select Sheets(I).Move ActiveWorkbook.SaveAs Filename:= _ ThisFN, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False Next ws End Sub
View Replies!
View Related
Making Workbook Opened From Other Workbook Active
I have a workbook that is a formatted report that I need to insert data from a name variable "CSV" file. I have created a macro in this workbook that calls an "Open" dialog for CSV files that I can use to browse to and open the source file. The macro then is supposed to "select all" copy and close the workbook then make the original workbook active and paste the data starting at row 2. The macro "seems" to be working perfectly except when I do the open the macro then makes the original workbook active, selects all copies then wants to close the original workbook. How can I tell the macro that the csv file that I just opened should be the active workbook, baring in mind that the name won't be known before the time it's opened so I can't hardcode the "active. workbook (NAME) "route.
View Replies!
View Related
Insert Rows & Repeat Values Based On Corresponding Cell Value
I have a list of 130 names in column A. I have a number value between 0-10 in column B (next to the name). I need to insert the number of rows defined by the value in column B, below each row that I already have (if the value is 0, then the row needs to be deleted). The inserted rows have to be filled with the name value from the row above. For example - before macro: Joe Bloggs 2 Adam Wilson 10 Peter Andrews 0 Claire Burrows 6 After macro: Joe Bloggs Joe Bloggs Adam Wilson Adam Wilson Adam Wilson Adam Wilson....................................
View Replies!
View Related
Loop Through A Spreadsheet Insert Into Another Wb Then Email
I am trying to create a macro that loops through a spreadsheet, copies data over to another workbook then emails the new document. I have taken this code from an old macro that I found but cant seem to get it to work. This is the code Sub EmailAssessments() Dim strTempName As String Dim strForename As String Dim strSurname As String Dim strBookingNumber As String Dim strReportingTo As String Dim strBookingGivenBy As String Dim strJobTitle As String Dim dtmStartDate As Date Dim strEndDate As String ''Need to use a string as there maybe no end date Dim strDepartment As String Dim strTierName As String Dim r1 As Range ''For loop Dim intNumberOfRows As Integer ''Count how many rows.........
View Replies!
View Related
Insert Command Button On Spreadsheet
Using excel 2003. Cannot insert command button on a spreadsheet we have. Almost all icons in the control toolbox are unavailable when Im on this specific spreadsheet. Any new documents I open work fine. Is there some kind of protected spreadsheet that wouldnt allow for a button, picture, textbox label to be inserted??? I didnt make the original sheet but am just editing and improving it. I would really appreciate any advice you could give, I have attempted to attach a jpg showing the greyed out icons in my controltool box
View Replies!
View Related
Work Schedule: Insert The Numbers 1-6 Into The Spreadsheet Throughout The 14 Days For Each Employee
I developed a 14-day work schedule and I assigned each different job position a number. The different job positions are numbered 1-6 and are as follows: #1=5:30am-1:30pm, #2=6:00am-2:00pm, #3=9:30am-1:30pm, #4=12:00pm-8:00pm, #5=1:00pm-8:00pm & #6=6:00pm-8:00pm. Numbers 1,2,4,5 clock-out for a 30 minute lunch break, while the other numbers do not. My goal is to insert the numbers 1-6 into the spreadsheet throughout the 14 days for each employee, and have Excel calculate the total number of hours for each employee in the far right column. I would also like "V" & "H" to equal 7.5 hours. This would save a lot of time instead of going through and adding up the hours with a calculator
View Replies!
View Related
Clear Row Based On Checkbox
I have a worksheet with several rows (A3:H70). Column J contains checkboxes (1 in each row). I'm trying to develop a macro that will clear rows based on the value of the check boxes. For example, if the checkbox on J3 is checked, that row (A3:H3) will be cleared. If the checkbox on J4 is UNchecked, then that row will be skipped and so on.
View Replies!
View Related
Value For Variable Based Off Control Checkbox.
I have several checkboxs in an excel sheet, that if one is checked i would like it to make a change to a variable in a macro that will run when saving a file. for an example: if checked: Checkbox1 = NCE1 Checkbox2 = NCE2 Checkbox3 = NCE3 Checkbox4 = NCE4 Checkbox5 = NCE5 I have a bit of code that is like this.... ActiveWorkbook.SaveAs ("\marketing2PartageNonconforms" & sF1 & " - CO" & sF2 & " - FC" & sF3 & ".xls") I would like sF1 to change depending on which checkbox is checked. so if checkbox1 is checked, than sF1 = NCE1 .
View Replies!
View Related
|
TRACKER
