I'm making a fairly simple database system that logs students in and out of a computer lab using the bar code printed on every student's ID card. Can any one suggest a particualr model of barcode reader that is reliable and doesn't have any problems working with a VBA application? I prefer something that doesnt have to be swiped, for example, a handheld retail-style barcode reader, something that students can simple place they're ID infront of to capture the information without swiping or pressing any buttons. Are all barcode scanners the same (when it comes to reading the informatio nand transfering it to VBA). What I mean is, do I have to get a specific kind of scanner to match the barcodes we use?
I have some VBA code which opens a pdf file in Adobe reader based on a cells Hyperlink reference. I want to add some code to close all instances of adobe reader before I call the function to open the correct pdf.
I recorded what happens when I use the "from web" tool from the data tab. I then tried to modify it by allowing me to manually (with the inbox) put in the ticker that I want. When I run it, it does nothing. When I manually put in the ticker symbol for the company, it works.
For instance, I I were to replace
"URL;http://ca.finance.yahoo.com/q?s=" & TICKER & "&ql=1", Destination:=Range("$A$1")) with "URL;http://ca.finance.yahoo.com/q?s=ATVI&ql=1", Destination:=Range("$A$1"))
It would then work, but I would have to manually change it every time.
Here is the code ___________________________________________________________ Sub Macro1() Dim TICKER As String InputBox ("enter ticker symbol") Cells.Clear With ActiveSheet.QueryTables.Add(Connection:= _
How do I create a hyperlink in an Excel 2003 file that will open a PDF file with Reader?
I have an Excel file of hundreds of Customer names and invoice numbers. I have a directory of PDF files. The name of each PDF file is the customer name, a space, and the invoice number. I should be able to have a column in the Excel file that contains a formula that creates a hyperlink to open the corresponding PDF file.
I used the HYPERLINK function and built the path and file by concatenating information. When I click the hyperlink, I keep getting an Excel error: Cannot open the specified file.
Do you think this problem is something of my own doing or some "undocumented feature" in Excel?
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
I've developed a little software using Excel Macros & VB. To prevent people from accesing the code I protected the code blocking it from visualization. It seems not enough as an acquaintance of a friend cracked it in 25 minutes. Or so he says. So I'd like to know if there is a better way to protect the font code.
I have an excel file having part code,name,vendor and Qty ( Quantity ).
My problem is that I want to apply an excel formula to pick up that vendor code who have highest Qty of a part code.The condition is that S.No.should not be disturbed.This file is so large,but here I have taken an example,
I am dealing with several very large spreadsheets using VBA to do various things. I found that my code worked well, but was taking a long time to run. The biggest time consumer was my use of the AutoFilter features. I have since turned calculations to manual before my code runs and set it back to auto when my code is done running. What are the potential consequences of my turning calculations to manual and then back to auto?
I have previously used the following code to successfully pull out IE webpage source code for string manipulation.
Its a crude example to demonstrate the principle:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Public IE As Object Sub Sample() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True
[Code] ......
However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"
The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......
The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.
Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?
My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?
VB: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = False Then Cancel = True
I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).
And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.
I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".
At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):
I am trying to determine how to get the code below to fire whenever cell J10 is populated and do nothing when cell J10 is not populated but I can't quite get it. (Cell J10 is manually changed and is not changed based off of a formula)
Is there a standard way to organize sections of code with code tags? Due to the nature of my work, there is a lot of documentation involved. And, my spreadsheets rely heavily on VBA. These code tag sections will be going into a design specification, so that there is a layer of traceability from the document to the program. These are the general sections or item numbers that I've come up with.
1. Dimensions and Variables
2. Data
3. Processing the Data
4. Rules of analysis
5. Analysis
6. Formatting
I guess I'm looking for something standard like UML diagrams for DFDs, but with tagging code to break them up into sections?
I am using the code below that I got off of these forums to email a particular sheet in my workbook, but I need to strip all of the VBA code and the command button from the sheet being sent.
I have problem to change text code into numerical code using macro. i have data contain text code and i would like to convert it into numeric . each text code has dedicated numerical code for example I have 4 fluids with text code text code:
FW = Fresh water SW = Saline eater CW = connate water MW = Meteoric water
numeric code as follow
FW=1 SW=2 CW=3 MW=4
I would like to convert the text code into numeric code, it is easy if using excell , but it is routine job for me , i need to create macro to be more simple .
What is the most efficient VBA code for the following macro recorded codes? I wish to write more efficient code versus the lengthy, cumbersome macro recorder code.
1) Macro Recorder Code to Copy One Sheet to Another
I have been trying to track down why this message keeps on popping up, doesn't matter what excel file I open it always pops up, code will halt at different times in the code.
I am looking for VBA code by which the results can be obtained without having to run the code. For Instance, if Z = X*Y, I would like the code to automatically calculate Z for as soon as the value of X and Y are changed.
I have implemented the code for the FuzzyPercent code as a module and Excel will not recognize the function for the cell (=fuzzypercent($C$3,$B5,D$2)). I am using Excel XP.
Sub Increment() Dim Lr As Long Lr = Cells(Rows.Count, "A").End(xlUp).Row If IsNumeric(Cells(Lr, "A")) Then Cells(Lr + 1, "A").Value = Cells(Lr, "A").Value + 1 End If End Sub
This adds 1 to the previous cell and displays in the next available cell.
How could I make it sao that when this number enters in the cell it hyperlinks automatically to the master file?
Before this button was introduced I used this:
Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error GoTo Err_App_WorkbookBeforeSave
Dim hl As Hyperlink
For Each hl In Me.Hyperlinks If Wb.FullName = hl.Address Then Application.EnableEvents = False Cancel = True..............
I have code that inserts columns, inserts formulas, and then copies the formulas to the last row of data. It all works good but for some reason, the columns that are being copied, are being copied past the last row of data. It can always be determined how far down it will be copied. Examples: if the last row of data was row 4, then the formulas would be copied down to row 24, if the last row of data was row 54, then the formulas would be copied down to 254, if the last row of data was row 284, then the formulas would be copied down to 2284. I can not figure out how and wh this is happening, but whatever the last row of actual data is on the spreadsheet, there is a 2 being placed in front of the last row of data and the formulas are being copied down to whatever the last row is with the addition of a 2 in front.
Here is the code for the columns additions, and formulas:
If the last row of data was row 54, then columns O:S would have the formulas copied down to row 254.
How can this code be modified so the formulas will not be copied past the last row of data?
How do you get the code boxes to appear in these threads. I do not know how to do this. As you can see, all I did was copy and paste my code in this thread.
I have a worksheet named mylist, that I delete, then recreate everytime I run a specific macro. However, I have code in this worksheet that I require, so is there a way for me to insert code to "insert code" on the "re-creation" of the worksheet?
When I started using VBA, all the code for each module was shown in the code window. This was a bit cumbersome when looking for one sub in particular. Now when I load VBA, all the Subs and Functions have their own page, and can be selected from the right hand drop down list. This is much easier for entering code, and finding subs. However, it is sometimes useful to see 'the big picture', so how do I swap between the two types of display?
I'm trying to add buttons to an excel spreadsheet at runtime. Each row in the spreadsheet should have its own buttons. I was able to create and edit them with the ActiveSheet. OLEObjects.Add() function, but after that, when i was trying to create code dynamically to react on the buttons' click events excel crashes (actually it works for one button, but not if my routine for adding a new button and event code is called more than once in a row!)
The code below works if the AddCmdbuttonWithCode() is called once, but crashes if it is called two or more times. Excel tries to restore the document after the crash and the first button and its corresponding click event code is visible and works... but NOT the second button and its event code...
The only way I can create multiple buttons right now is by calling my method once per click, opening the vba editor, changing the parameters for my AddCmdbuttonWithCode() routine and execute it again. After that I have mutliple buttons in different lines which all work fine (so the concept seems to work).
The problem seems to be the insertLine method, since everything seems to work if i leave it out (except for the code generation of course, since this is done by this part of the code :-) ). Is it possible that calling the insertLine Method can't be called multiple times? I don't know... any ideas? Feel free to test my code - it's small, easy to understand and has comments.
'this code calls the method which creates the buttons in specific cells with specific names Private Sub CommandButton3_Click() 'the first call always works! AddCmdbuttonWithCode "Edit_111_111_114", 23
'the second one crashes excel AddCmdbuttonWithCode "Edit_111_111_115", 27 End Sub
I have a really nice blue colour that I want to incorporate into all my spreadsheets, but I cannot suss out what the RGB code is, is there some code that could do the trick?