Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Way To Set The Printer's Properties Using VBA


Is there a way to set the printer's properties using VBA?

Sometimes we set the printer for BEST quality to do photos or brochures. Well if we don't change it back, when we go to print a spreadsheet, it takes forever because it is set on best quality.

So......
I want to be able to set the print properties to normal using VBA.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Set VBA For Default Printer
I need an assistance in setting up a VBA for the default printer in my office. The issue is I want my spreadsheet to be set up to wherever I open the spreadsheet at any computers in the facility. I want the sheet to be printing out directly at the printer in my office only. Is there a way to do this?

View Replies!   View Related
Network Printer 'ActivePrinter' Of Object'_Application' Failed
I am having trouble printing a page to both a local printer (which works fine) and a network printer (which does not work) Both printers are installed on the Computers. The error message I get when I try to run the macro is Run-Time ERror '1004': 'ActivePrinter' of object'_Application' failed. The codes that I have used are

Application.ActivePrinter = _
"\SAUWTPFS01.sopac.pilkington.netDespatch Toughend & Batch reports (L164) on Ne05:"

and

Application.ActivePrinter = _
" \sauwtpfs01pauwtp0305 on Ne05:"

and

Application.ActivePrinter = _
" \sauwtpfs01pauwtp0305"

It works on the day I record the macro. As soon as the next day comes around I get the error message. When I click debug it is these lines that are highlighted in yellow. I have looked at this for several days on and off and I always get the same error.

View Replies!   View Related
Set Printer To Cell Value
I have a quality form that is produced from data on an Excel log file. I was wondering if it is possible to select a different network printer based upon the value of a certain cell.

I understand how to print using the local machines local printer but our quality department is located at one site and these forms need to be printed at our other locations.

View Replies!   View Related
PageSetup Properties Using Variables (Run-time Error '1004')
I am getting error:
Run-time error '1004': Unable to set the Orientation property of the PageSetup class

I am using the following

View Replies!   View Related
Set Properties Value
storedPath = .CustomDocumentProperties("PathCertString").Value

Although the question I'm about to ask is not related to Excel, but related to MS Word, the coding is similar.

The above code I used to set the properties value, but I get an error highlighting 'storedPath'. I speculate MS Word does not recognized this word. Is there another word or code that I can use to set the value in the MS Word document properties?

View Replies!   View Related
Set Worksheet Button Properties
I have a button on a worksheet that activates a macro, the macro is stored in a "personal.xlsb" file. This file is copied to several users computers so they can use the macro, problem is once the button is assigned to a macro from one computer all the other users can't use the macro. What can I do to make this macro work on all computers? (Less placing yet another button on the tool bar).

View Replies!   View Related
Print To A User's Default Printer?
Is there a Macro to automatically print the selected print area (print area is also established within the macro itself) to a user's default printer; whatever that printer may be? Note that this printer and/or printer name could change depending on the user.

I would like this to actually select and print to the default printer without Excel having to ask you. I don't even want the "OK" box to show up if possible. I just want them to run the macro, execute all it's duties, and print, all in 1 shot.

View Replies!   View Related
Set Up An If Statement That Where 'a' And 'b' Are Numerical Values
I want to set up an if statement that does the following, where 'a' and 'b' are numerical values.

If 'a' is not a multiple of 'b' Then
...
Else
...
End If

How can I write this so VBA can understand it?

View Replies!   View Related
Can't Change Combobox Properties (activex)
How do I set the font size property and special effect property in this code?

I was trying to answer a question on Experrt Exchange but Rory beat me to it and now I'm feeling frustrated that I can't get my solution to work.

Sub AddCombo()Dim rVals As Range, rCell As Range, lTop, lLef, lHeight, lWidth, lCount As LongSet rVals = ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)lCount = 1For Each rCell In rValsIf rCell.Validation.Type = 3 ThenlTop = rCell.ToplLeft = rCell.LeftlHeight = rCell.Rows.HeightlWidth = rCell.Columns.WidthWith ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1", Left:=lLeft, Top:=lTop, Width:=lWidth, Height:=lHeight).Name = "NewCombo" & lCount.ListFillRange = rCell.Validation.Formula1.LinkedCell = rCell.Address(0, 0)'.SpecialEffect = fmSpecialEffectFlat //doesn't work //'.FontSize = 14 //doesn't work //'.Font.Size = 14 // doesn't work //End WithlCount = lCount + 1End IfNext rCellEnd Sub

View Replies!   View Related
Change The Printer Proprties Using VBA
i am using windows xp, and i am printing from a matrix printer, so i go to file then print then on the top right of the dialog box it say properties, and i go there to change the printers speed and darkness, the i click ok to print.


View Replies!   View Related
Choosing Specific Printer In Vba
In my program I want some labels to be printed on printer P661 (black/white) and other labels on printer P656 (color).

Q: is it possible to do this in vba for my code?

The labels are made in Word and printed from within Excel.


Sub PrintNACL_LABEL()
Dim oWord As Object
Dim sPath As String
Dim iCnt As Integer

sPath = ThisWorkbook.Path & Application.PathSeparator & "NACL_LABEL.doc"

iCnt = Val(InputBox("Hoeveel exemplaren?", "NACL_LABEL", 1))

If iCnt >= 1 Then

Set oWord = CreateObject(Class:="Word.Application")

With oWord.Documents.Open(sPath)
.PrintOut Background:=False, Copies:=iCnt
.Close False
End With

oWord.Quit False
End If

Set oWord = Nothing
End Sub

The specific printers in the network are:

Color ---> "\PlantijnP656 op Ne00:"
Black/white ---> "\plantijn2000oldP661 op Ne02:"



View Replies!   View Related
Printer Detection Macro In VBA
i have a workbook system that is used on 25 standalone machines that all have different printers everytime i install on a new system i have to reconfigure all of the printers that i have set up in VBA i am curious to find out as to whether or not there is a macro i can use that will generate a list of all printers on a machine that i can then use to refrence a printer name in my VBA

View Replies!   View Related
VBA Code And Cutepdf Printer
I'm trying to save excel files as pdf using vba code and the cutepdf printer. If I do this manually it works, but when I write code it saves the output to a file that cannot be read or opened

Unfortunately I don't have access to the pdfdistiller so can't use this technique which I've seen described in a number of forums.

The code I've used is below.

[PHP]Sub pdfing()

Dim cntTrue As Long, cnt As Long
Dim rng As Range, bk As Workbook
Dim fName As String
Dim WB As Workbook
Dim FundName As String

Workbooks.Open ("S:SRQFSFCWNames")

Range("A1").Select

Do
FundName = ActiveCell.Value

fName = Dir("S:SRQFS" & FundName & ".xls")
Do While fName <> ""
Workbooks.Open ("S:SRQFS" & fName), UpdateLinks:=0
cnt = cnt + 1
fName = Dir()
Application. ScreenUpdating = False
Loop

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

Range("A3:X3").Select
ActiveWorkbook.Save

View Replies!   View Related
Install Network Printer Via VBA
I have a new workbook that needs to be printed to a specific printer regardless of who the user is. I am happy with the method used for doing this using the PrintOut function, but am trying to work out what to do in the event that the user does not have this printer installed. The logic, not in true VBA syntax, is as follows;

' Check Printer
If <This Printer> Is installed Then
Printout using <This Printer>
Else
Install <This Printer>
Printout using <This Printer>

I found a useful post about ascertaining whether or not a printer is installed, but can't find anything about actually installing a printer via VBA.

View Replies!   View Related
VBA Code To Print To Specfic Printer
I have a macro that prints out several worksheets. My problem is that I want to print these worksheets to a specific color printer. There are many different users and most of them will have a black-and-white printer setup as their default. I want to be able to print using the ip address of the color printer.

I have tried setting the active printer using:

Application.ActivePrinter = "\WP010110.117.3.36"

with little luck... This seems like such a simple thing! I have done a lot of research and can't find a good solution although I have seen the question raised several times!

Ozgrid has been a great source of information and solutions in the past. Please help again!


View Replies!   View Related
Reset Default Printer Settings In Vba
We have printer mailboxes setup for all of the employees and sometimes the print settings get saved in the excel files. This means that when another employee uses the file their prints get sent to the wrong mailbox. We have hundreds of files like this and it causes all kinds of problems with prints going to wrong mailboxes!
Obviously their default print settings are all set to their own mailboxes.

Is there some way, using vba, to get an excel file to go back to using the user's default printer settings instead of the ones saved in it's file? (or get it to remove the printer settings saved in it's file). That way I could just make an application level open event to fix the problem! Either that or some way, in vba, of changing a user's storage box number for a specific printer.

View Replies!   View Related
VBA Print Preview Code Causes Crash If No Printer Installed
activesheet.printpreview , Excel crashes if there are no printers installed on the machine. How can i check for printers before calling the printpreview, or else stop the program from crashing?

View Replies!   View Related
Runtime Error '91': Objectvariable Or With-blockvariable Not Set
I get this error message (I hope I have translated it correctly from German to English) when running the following

On Error Resume Next
oExl.Workbooks.Open filename
If Err > 0 Then
MsgBox "Could not open Excel file.", vbExclamation, "Fileerror"
Exit Sub
End If

On Error Goto 0

With oExl. ActiveWorkbook.Worksheets(1)...............

It looks as if the Blockvariable is set. The interesting point is, that the error occurs when getting col1, not lastcol. I do not really see a difference between requesting lastcol and col1. col1 and row1 have been used and initialized previously. When debugging I also notice that the lastcol variable already has the correct value.

View Replies!   View Related
'Find' With Block Variable Not Set :/
I'm trying to find cells in a range that have a date >= today.... this wont work:

Set Cell = .Find(What:=Cell.Value >= Date, After:=.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

Can it be adapted at all?

View Replies!   View Related
Arrays As Object Properties--VBA
After much online digging, and experimentation, I think I determined earlier that you can't really expose an array all-at-once if it is an object/class property, as you can with an array variable. Now I'd like to confirm this, if I could, because it would be neater to run my simulation in an object instead of a giant and labrynthine user-defined type.

I try the following ....

View Replies!   View Related
VBA - TreeView Properties In UserForm
I have a tree view control property in a userform working fine, however, when the form open, only the top level is list (ie, only 1 string is visible). In order to see more, then user needs to click on the + which is not an issue, but they would rather have the tree open up to certain level, say 5 hierarchical levels down, instead of just the one string.

View Replies!   View Related
Is There A VBA Way To Open Print Properties Dialog
I know I've seen where the print Dialog window can be opened.

Is there a way to open the Print PROPERTIES Dialog window?

View Replies!   View Related
Error 1004 Using Range.Cells() Properties In Vba
I keep getting the following error : Run time error'1004'; Application-defined or object-defined error when using the following

View Replies!   View Related
VBA Read/write .MP3 & .WMA Properties()
I have had a long-held belief that Excel/VBA methodology is very suited to solving this problem. I have investigated several possible methods noted below and picked one that gives a "quick win". I offer my results so far as a step towards the goal, as well as giving an example of how to manipulate a non-MS Office application using VBA. In this case Windows Explorer - I have used similar code on corporate applications like Oracle and SAP. My 'Write' method uses Sendkeys. Luckily I have been able to do the job without having to use code to simulate mouse functions or using API calls to simulate key presses. Ideally I would like to use something more stable, but there is the bonus that it is simple, as well as changing WMA and both versions of MP3 tag (see below) if present. Perhaps on reading this someone else may have a better method.

Being a ballroom dancer I have a large collection of CDs as well as software to rip to hard drive and enhance the sound quality. Many are quite old. A big problem has been to get a consistent view of the file properties - especially Genre, (eg. Waltz, Foxtrot .. etc.) which, from my own CD burning or external sources, is missing, or incorrect from using the now obsolete ID3v1 tag standard list. Applications such as Windows Media Player and RealPlayer allow functionality to edit tags but become very tiresome when it comes to making bulk changes - such as after burning a new CD. When viewing properties of the same file in various other applications they often show things like Title & Artist switched, and Genre not at all. I have tried software to change MP3 file tags, but find them over-complicated, confusing, and difficult to make the bulk changes I need. With Windows Explorer we can only change 8 properties - but I find these sufficient.

My method is :-

1. READ : Run one macro to put data into a worksheet.....

2. MAKE CHANGES : Make manual changes to the Excel worksheet in the normal way....

3. WRITE : Run another macro to read the worksheet and update the file properties in Explorer.

Part 1 is very simple and robust. Part 3 is difficult because when we use Sendkeys to mimic keyboard entry the code runs too fast to allow time for things to happen on screen, so we have to put Wait statements *depending on how fast the computer runs*. So Slower is better - up to a point.

Properties are added to MP3 files by using a "Tag" - additional bytes of information which form part of the file. WMA files are a Microsoft invention using a similar, but different structure. Interestingly, using my code to make changes via Window Explorer updates BOTH MP3 Tag versions as well as .WMA files. I moan about Microsoft less and less.

MP3 *ID3v1* consisting of 128 bytes always at the end of the file is now 'obsolete' - despite being still in use. This is very easy to read/write using the same code as for Text Files eg.


Open "c:myfile.mp3" For Binary As #1

etc. It is, however, limited to 4 text fields of 30 characters max, 'Year' =4 characters, and 'Genre' is a single character, the Asc() code of which is a lookup to a standard list which contains 125 items - none of which is any good to me. Could have my own lookup I suppose.

MP3 *ID3v2.3* is in the process of being superseded by ID3v2.4. The big problem here is that there are several different versions and the code required is extremely complicated - mainly due to the use of variable length fields. So we not only have to find the property, but read the field length before getting the field contents. Writing would need to change the coded field length. This is further complicated by there being the option to use an "Extended Tag" - *or not* ! Version 2 tag can be at the beginning or end of the file (before ID3v1 if it exists) - or both. The MP3 files on my computer all seem to have both versions - v2 at the beginning and v1 at the end. It is further complicated by the ability to have User Defined fields. I see some of my files have a user defined 'Genre' field, despite having the standard one 'TCON' too. The tag also needs a form of "encryption" so that the mp3 player does not treat it as audio data. Visit here for detailed information http://www.id3.org .

To view an audio file in its raw state open it in a Text Editor. I use 'TexPad' which is very fast and gives a choice of Binary (with Text 'translation' in a column) or Text view. Notepad gives just a Text view with empty space for non-text/binary characters. There is a large number of Null characters Asc(0) in proportion to the overall file length - mainly for "future development" I believe.

I would be interested to hear of any comments, suggestions and code improvements.

READ CODE IS IN THE "REPLY" BELOW

View Replies!   View Related
Vba: Changing Properties Macro On Control Toolbox Item
I have a bunch of Option Buttons in a worksheet whose properties i need to change by running a macro. For example, I created an OptionButton1 in a worksheet using control toolbox. Now I want to run a macro to change it's name, groupname, linkedcell and caption. I did this macro but it doesn't work:

Sub Label()
Dim Code1 As String
Dim Link1 As String
Dim Form1 As String
Dim GroupName1 As String
Dim Caption1 As String
Worksheets("Section1").Select
Form1 = "OptionButton1"
Code1 = "FButton1"
Link1 = "FLink1" 'Defined in worksheet
GroupName1 = "FGroup1"
Caption1 = "Choose Function 1"
With Worksheets("Section1").Form1
.Name = Code1
.LinkedCell = Link1
.GroupName = GroupName1
.Caption = Caption1
End With
End Sub

View Replies!   View Related
VBA- Can't Paste Into A Newly Selected 'activecell'
I am new to VBA and was just wondering if there is logic behind this or am I doing something wrong....

ie,

1 Cells(1,1).Select
2 Activecell.Copy
3 Cells(2,1).Select
4 Activecell.Paste

The above doesn't work but if I use the same above 3 lines with

4 Activesheet.Paste

it does work

What is logic here as to why it can't paste into a newly selected 'activecell'?

View Replies!   View Related
Run-time Error '1004' :: Method 'Range' Of Object'_Global' Failed
I am trying to create a macro in my personal macro book such that whenever any workbook is opened the calculation settings (tools, options, calculation tab) are set to semiautomatic and do not calculate before save. The macro works when I am opening Excel itself (Book1) but when I open an already saved file it gives me

Run-time error '1004'

Method 'Range' of object'_Global' failed

From there I choose Debug, the VBE window comes up, and I hit F5 to continue the code without doing any actual debugging. Here is the code that I am using. This is in the Personal Macro book on the "This Workbook" section....

View Replies!   View Related
Run-time Error '1004' Method 'Range' Of Object '_Worksheet' Failed
I have an interesting error that only happens when there is one row of data in the worksheet (sheet2 or "Half Payout"). Rows 1 & 2 are headers, row 3 is when the data starts - if any. With either no rows of data or more than one the coding works just fine. Here is the exact error message I'm getting: Run-time error '1004': Method 'Range' of object '_Worksheet' failed.

The following code is supposed to sort the rows of data when opened and then activate the first open cell below B2.

View Replies!   View Related
Run-time Error '1004': Methd 'Range' Of Object '_Global' Failed
I have constructed the following code to set the print area of worksheets that have been selected to print to the range referenced in a worksheet level named range "xPrintArea". This named range is set using the OFFSET function. The procedure also sets the left footer to be a copyright notice that is also contained in a cell referenced by a named range.

Set oPrintArea = Range(sPrintAreaName)

is generating the error "Methd 'Range' of object '_Global' failed".

Note that the line

Set oCopyrightNotice = Range("CopyrightNotice")

does not generate this error. From what I've been able to determine from other research on this forum and others, I believe the problem is that I need to more fully qualify the object which Range(sPrintAreaName) is referencing. I've already tried to use

Set oPrintArea = wkSht.Range(sPrintAreaName).................

View Replies!   View Related
Run-time Error '1004': Method 'Worksheets' Of Object '_Global' Failed
I am not familiar with Excel/VBA and I tried a couple of suggestions with no luck. The excel file contains three forms and three modules and it is intended to calculate and build Electrical panels. I didn't write the code. I am just trying to fix the error. I was trying to attach the .xls file but it is 178 KB. How can I post the whole code.

View Replies!   View Related
Method 'Add' Of Object 'CommandBarControls' Failed (run-time Error '-2147467259')
i get this error on the line in bold Method Add' of object 'CommandBarControls' failed (run-time error '-2147467259'). im not sure why its giving that problem, but im not very fluent in custom toolbars.

'//The following two procedures add a custom menu to the workbook programmatically//'
'//and then delete it//'
Public currentMonth As String
Sub CreateMenu()
Dim mybar As CommandBar
Dim myControl As CommandBarControl
Set mybar = Application.CommandBars.Add( Name:="CustomButtons", _
Position:=msoBarBottom, Temporary:=True)
mybar.Visible = True
Set myControl = mybar.Controls _
.Add(Type:=msoControlButton, ID:=1)
With myControl...........................

View Replies!   View Related
VBA Code Run-time Error '1004'
Sub paste()

' paste Macro
' Macro recorded 2/20/2009 by lawryad

Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.paste
ActiveSheet.paste
Application.CutCopyMode = False
ActiveSheet.Range("p1").Copy
ActiveSheet.Range("a1").Select
End Sub
Now everything works good with this code unless there is nothing copied to paste. In that case it will go to a "run-time error'1004' " I would like instead for a msg box to appear saying "You must copy required data first"


View Replies!   View Related
VBA Replace Method Retains 'Within Workbook' Setting
I am selecting a block of cells to apply numerous "Replace" functions to.
So I use the Selection.Replace command - for example:

Selection.Replace What:="Street", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

if I have been working with Excel previously and did my own "Replace" and applied it to the entire Workbook (i.e. changed the within field from ' Sheet' to:
"Within: Workbook"

Then that field stays set to "Workbook" for subsequent Replace activities, including my VBA code !! Therefore if I do not manually go back and run one "Replace" and set the within field back to "Sheet", the VBA code will apply my Replacements to every sehhet and every cell in the entire workbook. Even if I have selected a Range of cells before issuing the command !! It ignores the selected Range and runs the "Selection.Replace" for the entire Workbook.

The "fix" I found on another site is to run a dummy command:

Set dummy = Worksheets(1).Range("A1:A1"). Find("Dummy", LookIn:=xlValues)

Which works. However, I am looking for a way to add a parameter to the "Selection.Replace" command that will cause it to search using the "Within: Sheet" setting. Otherwise I always have to remember to add that dummy line of code for every single Selection.Replace line of code.

View Replies!   View Related
Web Style 'Back' Button VBA Code
I have been trying to get a back button to work on an 2003 excel document that works like a website.

In the web toolbar there is a facility to go back and forward (very much like using internet explorer). I want a button on the actual sheet that does this command. If i record a macro and click the web 'back' button it records the end result of the process, not the process itself -

eg if sheet 1 and sheet 2 link to sheet 3 and I click a hyperlink from sheet 1 to sheet 3 then create a back button on sheet 3 and record a macro by clicking on the web toolbar back button the macro records the end result - eg Application.Goto Reference:="Sheet1!R14C8". However if i then go to sheet 3 through sheet 2, the back button I created takes me to sheet 1 not back to sheet 2.

Anyone know the code I could use to make it actually go 'back' to the previous page?

View Replies!   View Related
VBA - Saving An Email Only After 'Send' Is Pushed?
I have a spreadsheet that automatically creates an email (based on code from rondebruin.com and some manipulation from a consultant) and initially would just save a copy of the message on our network (for records purposes) as well as sending it.

However it occured to me after that there will be times when attachments are required and as such for archiving purposes it really needs any attachments to also be saved as part of the email. What I believe to be the relevant code is shown below:

With OutMail
.To = TextBox6.Value
.CC = ""
.BCC = ""
.subject = "MRWA response to your inquiry on " & ComboBox9.Value
.body = strbody
.Display 'or use .Send
.SaveAs "\Dacsrv02
tddataAdviceTech QueriesTRIM Emails" & emailname
End With
On Error GoTo 0

The thing is it is storing the email at the same time it opens it for display. As such any attachments would be made post save. How would I go about getting it to wait until after attachments have been made (presumably once it detects the 'Send' button has been pushed on the email)?

View Replies!   View Related
VBA Button To Clear 'unlocked' Cells
I'm putting together a time sheet that can used to calculate total hours worked by department etc. I plan on protecting the worksheet and only allowing certain cells to be selected and data entered.

Is there a way using VBA to place a button on the first spreadsheet that will clear all cells that are 'unlocked'?

View Replies!   View Related
Add-in 'Solver' To Automatically Find A Value In Vba
I would like the XL add-in 'Solver' to automatically find a value 'M' in the equation attached, based on 'R' being 30%.

I also would like to be able to manually enter 'M' based if I would like 'R' to be different from 30%.

How can I program this in VBA, and have the equation automatically done in the first instance simply by clicking on cell 'R'.

View Replies!   View Related
Using 'IF' With Conditional Format (change Cell Color Based On The 'P' Or 'F')
My cell has a 'P' or an 'F' plus some additional text. I'm trying to change cell color based on the 'P' or 'F' but I can't get it to recognize the letters.
Samples of what I've tried:
=IF(LEFT(L23,1)=P)
=IF(MID(L23,1,1)=P)
etc.

I can 'FIND' the 'P' or 'F' but there may be a P or F else where in the cell so the color may come up incorrect. I'm trying to get it to just look at the first character in the cell. Can the 'IF' be used with CFs?

View Replies!   View Related
How To Restrict The Cell Data To Letters 'a' Through 'e' AND 'A' Through 'E'
I am trying to come up with an excel shee which can track the progress of a student on practice exams. This exam only has multiple choice questions, with answer choices A,B,C,D or E.

What I am looking for

1) I want to restrict the cell data to A,B,C,D or E (both cases). Note that I am not looking for a drop down/combo box. It would take the user to use one or two mouse clicks to drop down the list, and I am not in favor of that.

2) I would like this validation/formula to be applied to all the cells in a particular column.

3) Nice-to-have.

If the user enters a lower case letter, then I would like to convert to upper case.

View Replies!   View Related
Using VBA To Enter Date When Other Cell Is = '0'
when the number in cell 'A1' reaches zero i want the VBA code to enter the date in cell 'A2' that it became '0'.

This is as far as i've got.

Dim keyRange3 As Range
Set keyRange3 = Range("L:L")
If Not Intersect(keyRange3, Target) Is "0"
Target.Offset(0, 2) = Time
End If

View Replies!   View Related
VBA To Fill References ='Account (1)'!$C$5
I need to produce a grid displaying data pulled from local references within the workbook but Excel will not auto fill the references in the right way.

i need

='Account (1)'!$C$5
='Account (2)'!$C$5
='Account (3)'!$C$5

down to

='Account (498)'!$C$5
='Account (499)'!$C$5
='Account (500)'!$C$5

is there a way to imput these references using VBA script?

View Replies!   View Related
Fill Tab Doesn't Go On To The Next Set Of Data
what do i need to do when the fill tab doesn't go on to the next set of data. It is just repeating the data in the same column from the first calculation--it doesn't go on to the next set..what do I need to change when inserting my calculations...

View Replies!   View Related
Correct Way To Set-up A Spreadsheet So It's User Friendly
attached is a sample schedule, there is conditional formatting in all of the cells (see graphic) such that if the cells to the left are the same value, then the cell is turned white, and a border is removed.

This gives the appearance of a 'block of time' while which cleans up the appearance of the spreadsheet.

the problem i have is that when we scroll to the RIGHT, we lose track of the 'left most' cell...which is black of course, the similar ones to the right are intentionally whitened out.

a perceived solution would be to change the conditional formatting of the column (actually only the cells in the column we care about) when it is NEXT to the vertical freeze pane so that it appears not in white, but in black font, etc.

the TRICKY part is that we will obviously need to go back and forth, so the columns will need to change formatting often.

or

when the column number (or letter) next to the freeze pane changes, change the formatting to certain cells in that column to an alternate version; and change the formatting of all other certain cells within other columns to original formatting[

View Replies!   View Related
Can't Stop Macro Running At Set Time
I wanted to run a macro at 08:00 each day. I tested the macro would auto start and run, and set the test to start at 13:00.

In the Private module of the Workbook I put

Private Sub Workbook_Open()
Application .OnTime TimeValue("08:00:00"), "MyMacro"
End Sub

and in a standard module

Sub TestTimeMacro()
Application.OnTime TimeValue("08:00:00"), "MyMacro"
End Sub

Initially I had the TimeValue set as 13:00:00. The test worked OK so I changed the timevalue to 08:00:00.

The macro now runs at 08:00 and 13:00.

How do I stop the 13:00 event?

View Replies!   View Related
Run-time Error '1004' Method 'Add' Of Object ' Sheets' Failed Adding Multiple Sheets
I have been running a simulation for about 18 hours now and just received:

Run-time error '1004':
Method 'Add' of object ' Sheets' failed

I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:

ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)

The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...

View Replies!   View Related
"Run-time Error '1004', Method ' Range' Of Object '_Global' Failed"
I keep getting this "Run-time error '1004', Method ' Range' of object '_Global' failed" Here is the code that has the problem:

Option Explicit
Dim i As Long
Dim j As Long
Dim lDup As Long
Dim lRow As Long
Dim NoDupes As Collection
Dim rRng As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Swap1 As Variant
Dim Swap2 As Variant
Dim wks As Worksheet

Private Sub UserForm_Initialize()
Call DefaultSet
For lDup = 1 To 3
Call NonDuplicatesList(lDup)
Next lDup
End Sub.....................

It does not even loop once though the original UserForm_Initialize For/Next loop.

View Replies!   View Related
Run Time Error 1004 - Method 'Range' Of 'Object'_Global' Failed
I am trying to build a macro which will format the columns of a spreadsheet - basically it inserts some columns, writes formulas and highlights them. Here is a code I have got so far...

When I try to run this I get a run time error 1004 - Method 'Range' of 'Object'_Global' failed. The part of the code
Range("N2:N").FormulaR1C1 = "=(RC[-7]/RC[-2])"

is highlighted in the debugger.

Can anyone tell me why this is happening, also it would be great if you could suggest better ways of writing this code - as I am new to vba programming and most of my macros are built using the recorder and then 'working' on them.


View Replies!   View Related
Module To Find & Move Based On Cell Adjacent's Set Value
ABCDE
FGHIJK

If the above is in different cells, I want to be able to move the data in "G" to the cell beside "E" (up a row and over 4 columns) IF the cell to the left of "G" equals what I specify is in "F" (for example use "product"). I would want to apply this to an entire sheet, not just once. If the macro (not sure what its called) could also then delete the row that contained "G" after it moves it, that would be even better! The output in the example above would be:

ABCDEG
F HIJK

View Replies!   View Related
Create From The Data Set In Column Set 1 The Data Set Shown In Colunm Set #2
My data is in colunm set #1. I want to create from the data set in column set 1 the data set shown in colunm set #2. I want to automate the process. The program needs to regognize if a name appears only once or more than once. If it appears more than once the data needs to be merged as I highlighted from yellow to green.

View Replies!   View Related
To Set Up A Condition Is Vba
Been teaching myself VBA and I've gotten stuck, and no matter how I search I can't seem to find an answer...

Trying to set up a condition is vba where if a cell value minus another cell value is greater than a static value (elsewhere in the sheet), then to turn the font green.

Code so far:

Dim Cval As Integer
Cval = Cells(3, 6).Value
FinalRow = Cells(65536, Cval).End(xlUp).Row
For i = 10 To FinalRow
If Cells(i, Cval) - Cells(i, Cval + 1) > R6C5 Then
With Cells
.Font.ColorIndex = 10
.Font.Bold = True
End With
End If
Next i
End Sub

I can't work out the right sintax to get a if condition to work correctly.

I have gotten this to work with conditional format, but if you want to copy/paste elsewhere, it doesn't paste the format result correctly...

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved