Assigning Inputbox Value To Cell Works In 2004 Not 2007
Nov 20, 2009
I'm working on a tracking spreadsheet that needs to be able to function on both Excel 2004 for Mac as well as Excel 2007 for PC. The code below is written to ask for a date when the status of a particular row of cells is changed to a value other than "". These cells have validation lists where the list itself is just below the cell in hidden rows. It then places the date entered in the corresponding cell to the right of the list choice in the hidden rows and the top row is simply a vlookup to show the date with the status choice.
This way, when the status cell is changed again, it retains the date in the hidden rows while setting a different one. The code below is obviously not the full code, the whole code includes other functions and is a bit long, but I've narrowed it down to this:
It works great in xl2003, and it works properly in XL2007 until you try and change it. I changed the column F to Column D and now it gives me a #value.
The below code works fine in excel 2003 but when I run it in excel 2007 it crashes excel. (Microsoft Excel has encountered a problem and needs to shut down etc etc)
In Excel 2007 I am creating a macro with the "relative references" setting turned on. I want to repeat some tasks like: go to column A, sort, hide columns B thru F, hide columns H & I, return to column A.
Every time I try to create this macro by capturing key strokes, it inserts the workbook / worksheet name in the macro! Kinda defeats the ability to use it in any other workbook.
I am recording the macro in the PERSONAL.XLSX workbook. I have other macros that successfully perform in any workbook they are used in . . . but today I can't create one that doesn't incorporate the name of the original sheet it was recorded in.
In an effort to manipulate the xml documents within an xls, you used to be able to change the extension to .zip then open. This no longer works for me, just getting the 'cannot open file: it does not appear to be a valid archive.
I have tried it with a number of different files, but no joy.
It still works with a .doc so I guess it's not that Microsoft have stopped this capability?
I would like to convert them to AppleScript so I can use them in Excel 2008.
From what I understand I should be able to do this with AppleScript, but I have never used it before and have absolutely no idea what I am doing.
Not only do I need to know how to convert the VBA to AppleScript, I need to know what to do to make it run in Script Editor, etc.
There are two VBA Macros, each is to highlight a specified cell range, then turn on cell protection.
It is my understanding that although I couldn't record this into my VBA Macro, AppleScript will also let me unprotect the worksheet at the beginning of the script, and then re-protect it at the end of the script.
Can any Mac users out there confirm that if a sheet is protected that a user cannot group and ungroup rows using the "+" if the sheet is protected using the following...
With Sheet1 .Protect Password:="******", UserInterfaceOnly:=True .EnableOutlining = True End With
It works fine on my PC Office 2003 but I hear it won't work on a Mac but I can't confirm it.
I have the inputbox so i can set a string value, When the inputbox Cancle button is pressed i want to exit sub, If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname. If the inputbox has a value do >>>>>>That>>>>>
I am on a Macbook Pro, using Excel 2004 for Mac ...
I have used Conditional Formatting to determine duplicates identified by a color other than black. Is there a way to sort a list according to the color of the text?
I have a number of cells which are connected to various formulas. EX. A1 = 7. Well I want a formula in A2 which can look at A1 and if A1 = 5-6, then A2 = 1, if A1 = 7-8, then A2 =2, if A1 = 9-10, then A2 = 3, if A1 = 11-12, then A2 = 4. And so on.
I am working on a production spreadsheet, and I'm trying to figure out the easiest way for my coworkers to enter data into the system. Each shift, we produce from 5-20 different varieties of chips, and we keep track of how much we've made of each. I've decided that a simple ADD and DEL button at the end of each row will work (unless anyone can think of something easier or better, let me know pls).
Cell G3:G30 is where data will be stored for each variety. The default value for each variety is stored in column C. I found a very basic macro that adds a value to a selected cell, but I wish for it to find the default quantity for the variety in that row (changes day to day as per our schedule), and add it to the value in column G. The DEL button will do just the opposite.
I have 3 worksheets that I need this macro in as well (days, afternoons, midnights), but I don't think that will be any problem. I've searched around for a solution, to no avail.
I am using the first part of the code to select the last cell in a column. I wish to enter that cell into a InputBox, rather that the default "A3" shown below. How do I modify the second part of the code?
Dim strName As String Sheets("Prices").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Select Set wSheetStart = ActiveSheet strName = InputBox(Prompt:="Type in column A cell to paste component into", _ Title:="Price List Paste", Default:="A3") Range(strName).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False
I have a macro which retrieves data from web.It works fine.I just don't know how to assign a cell value in web address.i have attached the code here. i just want to assign activesheet.(range"c2").value for "q?s".
I'm attempting to create a macro that will look at the total in column (K) and send an e-mail to two different addresses, depending on the amount. If the amount is over $10,000 then one address (over@macro.com) if under, then the other (under@macro.com)
My main problem is that I never know what cell the total is going to be in.
I currently have all quotes going back to the person that sent in the request, no matter what the amount. Their e-mail is in the sheet.
So what I think I need to do, is find the last cell in column K with data and assign it a variable. If that variable is over $10,000 then I can send the e-mail to a hard coded address. If it's over, I just use my original code.
I have made a macro to nominate a % rate in an input box , then have a value multiplied by this rate. Now i need the macro to place the rate followed by a '%' symbol in the cell below.
I've been working on a payroll program for my small biz. I'm close to done BUT...
When I use the Index Match function in a cell it works great. However I need to copy it down a column to work for pay periods going forward. When I copy it down of course it puts zeros or N/A.
Columns:
Gross Fed FICA Medicare State Net F G H I J K
Gross is user input. FICA Medicare State and Net are simply calculated on the sheet. But to get Fed Income tax I use the Index Match function and it works perfectly. The rows increment properly with each new user input.
This is the code in Column F. You can see where it uses the result in Column G to look for a value in sheets("SingleWH").
My question: Fed Income tax is dependent on what the Gross is in order to look up the tables with the Index Match function. How do I get the result of the function into column G? Can I increment up Column G and ignore the zeros that dragging the function down G creates?
I have written the code below. And I found two problems:
1.The code stopped to work when I change from Paste to PasteSpecial.
Sub Copyfriction() Dim DestWB As Workbook: Set DestWB = ActiveWorkbook Dim DestWS As Worksheet: Set DestWS = ActiveSheet Dim DestCLL As Range: Set DestCLL = ActiveCell Dim UserChoice UserChoice = Application.GetOpenFilename(FileFilter:="Text Files (*.xls),*.xls")
[Code] ........
2.As you see the right cell on the the destination sheet need to be selected. So Im planing to run the macro when double clicking the cell in question. I have tried the next
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Address = "$A$6" Then
I am trying to assign an array element to select a specific cell then assign a value to it. Below is the general code that I am working with. Does anyone know why this is not working?....
I want to be able to specify inside the message the word that is in cell F6. So it would say "You Must Give A Reason For The Amount Of Mgr Voids For Shawn"... shawn being the name in cell F6.
If Range("F9").Value > 50 Then MyInput = Application.InputBox("You Must Give A Reason For The Amount Of Mgr Voids For This Employee") If MyInput = "" Then End If MyInput = False Then End ActiveSheet. Unprotect ("13792468") ActiveSheet.Range("F9").AddComment Range("F9").Comment.Visible = False Range("F9").Comment.Text Text:="" & Chr(10) & (MyInput) & Chr(10) & "" ActiveSheet.Protect ("13792468") End If
I have the below code that changes the colour of a cell to green if the figure increases, and to red if it decreases.
The issue is that it works fine if you manually change the figure in A1 or A2 by entering the figure and hitting enter. If the cell figure changes in A1 by any other means such as RTD or say a value in C1 "=C1" then the colour change is not triggered.
Code: Public oldval, myval
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:A2")) Is Nothing Then oldval = myval If Target.Value > oldval Then Range("A" & Target.Row).Interior.ColorIndex = 4
I am sending an email when the value of a cell changes, [url]
I am using the following code in my worksheet....
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Target.Cells.Count > 1 Then Exit Sub On Error GoTo EndMacro If Not Target.HasFormula Then Set rng = Target.Dependents If Not Intersect(Range("AE7"), rng) Is Nothing Then If Range("AE7").Value < 20 Then EmailOut 'MyMacroName End If End If
What I would like to do is increase the range that this applies to.
Range from AE7 to Range Y3:AE250.
I would like to Include the Range A3:A250 in the subject/body.
So If AE7 has changed, A7 (along with some text "This cell has changed, do X Y Z") would be somewhere within the email.
I have a spreadsheet with a VLOOKUP formula that I have to send to a customer weekly. They are required to type a code in column E of what they invoice, and it pulls the rate into column G based on a rate table I have. The formula is: VLOOKUP(E2,Rates!A:B,2,0)
When I made the spreadsheet, the formula worked perfect. But when they type their codes in and send it back to me, it's messed up. All of the codes they've typed in do pull the correct rate. However, if I try to go to column E and type a different code on top of what they typed (a code that IS IN the rate table), it will tell me: "The value you entered is not valid. The user has restricted values that can be entered into this cell".
If I create a new tab and mimic the exact formula and columns as I am trying to work with, it works perfectly. But for some reason, once they've typed their data into my spreadsheet, something messes up even though the formula is perfect, dragged down, etc. All columns are formatted as text. And the range in the rate table is correct as well. When I try to mimic the formula in the other tab, if I click a cell in column E, there is a drop down list there showing all the available codes from my rate table to choose from. But on the spreadsheet that is messed up has no drop down list on the cells.
There are 20 worksheets in my workbook. For the first ten, I would like to print out the only sheets in which cell E28 = 0. For the last ten, I would like to print out the sheets in which cell T1 = 0. Each sheet has a unique name.
