Web Form Interface- Amount Of Lines Affects Speed

Sep 18, 2006

I built a web form interface which I use to adjust orders on a web-based system our company uses.

The code I have looks at a sheet, checks if the line needs adjusted, makes adjustments as needed, and then clicks a box for lines needing ordered.

Here is the code I use:


Sub Adjustorder()
Application.ScreenUpdating = False
Dim ie As Object
t = ThisWorkbook.Sheets("Adjust").Range("C3").Value 'checks for valid orders to adjust
If t = 0 Then MsgBox ("No Skus to add"): Exit Sub
Set ie = CreateObject("InternetExplorer.Application")
With ie

This all works fine, but the amount of lines can be anywhere from 1 to 11,000. The fewer the amount of lines needed to loop through, the faster it does each line. With 11,000 lines it took nearly 1 second per line. With 200 lines it took 2 seconds for all of them.

View 9 Replies


ADVERTISEMENT

Excel 2010 :: Lines Count - How To Expand Amount Of Columns In One Spreadsheet

Dec 27, 2011

I'm using Excel 2010. One spreadsheet I'm using has 1048576 lines and is a XLSX file. The other file has 65536 lines and is also a XLSX file. When I try to do a VLOOKUP, I'm given an error stating "invalid reference. This file version cannot contain formulas that reference cells beyond 256 columns or 65536 lines.

If both are XLSX files how do I expand the amount of columns in the one spreadsheet with 65536 lines? I thought all XLSX files had a million lines.

View 4 Replies View Related

Show Table With Grid Lines In A User Form

Aug 11, 2009

Is it possible to show a table in a user form? For example I have a table that is 4 columns wide and 5 rows deep with lines seperating both. I have tried using a text box and list box to achieve the results I am looking for but can't figure out how to add lines to seperate data between rows and columns.

View 5 Replies View Related

One Workbook Affects All Workbooks

Nov 20, 2009

I have made a workbook file which is a form. I deleted all tool bars except "Print" and "save as". And I have saved this workbook. When I startup XL again to do other work It brings up my same bar arangement as my form. When I change it back it also changes my form file. How do I keep my form arrangement seperate from the standerd excel set up?

View 13 Replies View Related

Code Affects Screen Display!

Mar 2, 2009

I use the following piece of code to Hide/Unhide columns as required by a specific worksheet.

View 4 Replies View Related

Can A Cell Have A Formula That Only Affects Other Cells

May 16, 2009

What I need to happen exactly is this:

If cell BE72 says "Natural", then cell CM11 equals the value in cell BE73.
If cell BE72 says "Deflection", then cell CT11 equals the value in cell BE73.
If cell BE72 says "Dodge", then cell DA11 equals the value in cell BE73.
If cell BE72 says "Insight", then cell DH11 equals the value in cell BE73.
If cell BE72 says "Luck", then cell DO11 equals the value in cell BE73.
If cell BE72 says "Sacred", then cell DV11 equals the value in cell BE73.
If cell BE72 says "Compet.", then cell EC11 equals the value in cell BE73.
But, If nothing is entered in BE72, then I want to be able to type my own values in cells CM11 through EC11 without overwriting any formula.

Since I cannot have a formula in a cell and still type in it without overwriting the formula, I was wondering if it's possible to have another cell (maybe cell A1) that has this formula in it. Can a cell like A1 hold a formula that functions for other cells even though nothing happens in A1? If so, how would I make the above work?

View 9 Replies View Related

Zoom Level Affects Shapes Positioning

Jun 30, 2008

Sub CreateRectangles()
X = 1
Do Until ActiveCell.Offset(X, X).Value = 0
X = X + 1
Loop
variable = Round(-0.026 * (X * X * X) + 0.56 * (X * X) + 24.7 * X + 5, 0)

Set Box = ActiveSheet.Rectangles.Add(ActiveCell.Offset(0, 0).Left, ActiveCell.Offset(0, 0).Top, variable, variable)

With Box
.ShapeRange.Fill.Transparency = 0.3
.Font.Size = 18
.Font.Name = "Trebuchet MS"
.Text = ActiveCell.NoteText

End With

End Sub

View 3 Replies View Related

SAP Interface

Feb 19, 2008

I often need to retrive info from SAP and fill in an excel document with it. Is there a book or a web page where I can find macros that will help me do that?

View 9 Replies View Related

Calculated Field Deletion Affects All Pivot Tables?

Jul 2, 2014

I have my raw data in one Worksheet and have made multiple pivot tables based off this data. Since I needed to have the same calculated fields in all these Pivot Tables, I used to simply duplicate the pivot table sheet and edit the pivots to create a new table.

Now, I'm trying to create a new pivot table from scratch, by selecting the base data table and going to "Insert Pivot Table". However I see the same Calculated Fields and upon removing, the field is removed from all other Pivot Tables.

View 1 Replies View Related

Macro Copying & Pasting Chart Affects Its Size

Sep 13, 2006

I have a macro that takes a worksheet and copies it to another workbook. It works, but the graph that is contained in the new worksheet ends up a little screwy; for instance, the Plot Area is larger than it should be (height-wise along the screen), and the chart's legend isn't in the exact same place as in the original chart.

ThisWorkbook.Worksheets(tempSheet).Copy After:=wb.ActiveSheet 'before:=ThisWorkbook.Sheets(1)
RenameTemplate

'unlink copy from template
'must unprotect before unlinking. We'll leave it unlocked cuz we don't care.
wb.ActiveSheet.Unprotect
'cells
wb.ActiveSheet.UsedRange.Copy 'ThisWorkbook.Sheets(tempSheet).UsedRange.Copy
wb.ActiveSheet.UsedRange.PasteSpecial Paste:=xlPasteValues ......................

View 3 Replies View Related

Interface With Attachmate

Jan 1, 2008

I'm trying to write a macro in Excel to interface with "Attachmate".

My problem is when I do a "CreateObject" I receive error for active x can not create object.

I looked in the Excel Ref Lib for either "Attachmate" or "Extra". and could not find them.

Does any one know for sure if Excel can work with "AttachMate" if so can you provide directions.

I'm using Office 2003 Pro, Windows XP.

As a side note I've read of "AttachMate" can be coded to work with "Excel", but so far not the other way around.

View 9 Replies View Related

Formula- To Calculate The Amount Due Based On Cumulative Sales Once A Breakpoint Amount Is Reached

Jan 28, 2009

I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.

Example:

Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%

month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00

and so on...until the end of year.

I tried using an if formula by could not get it to work.

View 9 Replies View Related

Reference Cell & Add Amount If Positive & Subtract Amount If Negative

Jun 5, 2008

Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:

I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.

View 4 Replies View Related

User Interface For Calculations

Jun 8, 2007

I am trying to create a user pop-up box that can accomplish the following:

a. Pop up box that requests an input for a particular machine number, type of error and also a particular calculation type (STDEV,Average,etc).

b. The result will be displayed either in a tabular format or if applicable, a chart

I have 10 worksheets where each worksheet is a different mahcine ID. Contained within the worksheets is various data-sets(Data listed by week). I am trying to enable my mangers ability to not have to look through my data or choose a worksheet within my workbook. I would rather have a input screen that would give him a choice.

View 3 Replies View Related

Userform As A Standalone Vb Main Interface

Oct 17, 2006

In order to beutify my prog's interface, i would like to open the Excel file(active VBA) with the worksheet in the minimized background or hide, i.e. only the Userform or a Dialog window, from the right beginning on! as if it was a standalone VB main interface.

View 3 Replies View Related

Userform Interface For Holiday Planner

May 29, 2008

I have one workbook with two worksheets (Jan-JUly and Aug-Dec). I am using a userform to add data into worksheet ("Jan-July"). But I do not have any clue - How do I update the other worksheet (same data like name,join year etc)worksheet("Aug-Dec") at the same time. I used two worksheets because worksheet doesn't support 370 columns, and to make my life easy. UserForm add data into Column A to M (worksheets Jan-July), rest is done manually. I have also attached the file.

Private Sub dataAddButton_Click()
Dim dataCheck, eMsg As String
Dim strLastRow As Integer
Dim x As Double
strLastRow = xlLastRow("Jan-July")
'get Last Row
With dataInput
'dataInput name of userform
If (.fNameBox.Value <> vbNullString And .fNameBox.Value <> vbNullString And .joinYearBox.Value <> vbNullString) Then
'textbox validation value can not be empty......................

View 2 Replies View Related

Creating A User Interface For An Excel File

Jun 12, 2008

I have been tasked by the company I work for to come up with a simple to use interface that can be used by someone with little understanding of excel to set the autofilters to certain settings.

So here is what I have:

2 sheets (each with the same number of columns but different data which corresponds to good and no-good)

Each column has an autofilter but only two on each sheet need to be set.

One filter controls valve type (of which there are 10) and the other controls running condition (startup and normal)

Now my employer wants something that is basically idiot proof so that someone can simply open up the excel file, input the valve type and running condition, and be rewarded with only the corresponding data and charts. Now the filters are already in place, the charts already exist and the only thing left is the user interface.

View 14 Replies View Related

Userform As Printer Interface For Protected Workbook

Apr 21, 2014

I'm closing in on the final stage of a large project and I've run into a road block. The following line of code is preventing my users from being able to select specific page ranges when using the Print Entire Workbook option in the print menu.

[Code] .....

I've attached the latest version of my form (with significant support by jaslake) to explain what I'm attempting here. The attached form has several macros that activate upon open; including a user form. The password for the workbook/worksheets/VBE is "j".

Attached File : HCT-RCS ITP V1.89.xlsm

View 14 Replies View Related

Control Device Through GPIB Interface Using Excel VBA?

Jun 21, 2006

is it possible to control a device through GPIB interface using excel VBA?

View 4 Replies View Related

Interface Design: Drop Down List Sources

Jun 4, 2006

I am working on a workbook that contains drop-downs in various cells. The source for these drop downs are several Named Ranges on a tab named 'Drop Downs'.

I am locking the whole spreadsheet down so that people are not messing with the format, etc. (which is the current problem). So I don't allow people to add/delete rows, etc.

I am trying to think of a good interface to allow users to modify the entries in each drop down list (i.e. they may need to add a person to the "Resources" Range which feeds into a drop-down box elsewhere).

So, people should be able to add/delete items. I was thinking I would put a little 'X' next to each entry, such that when pressed - it would delete the corresponding entry. (Using the Assign Macro on a Text Box). However, I have to assign each 'X' a method to call - and I can't pass in an argument - so if I assign them all the same macro name - I won't be able to figure out which one was pushed. And the number of 'X' will be dynamic, so I can't hard code a bunch of functions. I don't really have a good idea for allowing additions yet (other than a button that prompts for the name and then adds it in).

I guess I could just use a Form that allows editing of the data (that would be easy and powerful to do), but I don't really want to do that - I want it to be editable within the excel spreadsheet itself.

View 6 Replies View Related

Work With Main Interface While Userform Is Open

Jul 18, 2007

I'm writing a program on VBA for Excel and I've succeeded in just displaying the Userform when opening the file. I have set the ActiveWindow.Visible option to False and the Application.Visible one to false as well so that i only have the user form displayed.

However, if i try to open an excel file via "My Computer", the file doesn't open. I also noticed that when I was leaving the application.visible option to True, i couldn't access the application while the userform was running. I think i need to do something with an event, but i can't find the event corresponding to opening a file oustide Excel.

View 2 Replies View Related

Macro For Base Amount And Net Amount Calculation

Nov 20, 2012

For the below macro.

ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030

A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.

For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.

View 1 Replies View Related

Nifty Email Generator / User Database Interface?

Apr 27, 2012

I've been trying to modify this code in several ways but I can't figure it out, I'd like to

a) use different sections for the email generation where the email address/surname/forename/location are in my user database

b) have a popup screen appear for the body of the email text, I don't like the idea of my secretary having to go into the code to change the text

c) another popup box requesting an email identifier that will set a 'flag' in the Email Information column to show they've had that email and (this may be ambitious) to record the body of the email text somewhere that I can review so there's a record of what's been sent out

This is the code

Dim oXlWkBk As Excel.Workbook ' Excel Work Book Object
Dim oOLApp As Outlook.Application
Dim oOLMail As MailItem
Dim lRow As Long
Dim olMailItem

[code]....

View 3 Replies View Related

Protect And Unprotect All Sheets And Workbook Using Userform Interface And Password

Jul 6, 2012

I am trying to create a way to protect and unprotect all sheets and the worbook by means of a Userform pop up interface.

I have managed to create the Userform but what the code will look like and where to place the VBA code that will make it work, Also i would like to automatically save the sheet/s as i switch between them. so that any updates made to the sheet will be automatically saved when i move to the next sheet and also when i protect or close the workbook.

View 3 Replies View Related

Pasting Into Filtered Cells Affects Other Cells Also?

Jul 24, 2013

Usually if I filter and perform some kind of operation on the cells displayed by the filter it is only these cells affected. For example, if I run a filter that shows 10 rows and I Paste a value into say, Column A, of all 10 Rows then remove the filter and look through all my rows, only the 10 cells displayed during the filter contain whatever I Pasted in.

However, sometimes when I do this many rows that were not displayed as part of the filter are affected. For example, I Paste something into my 10 filtered Rows and after removing my filter I find that whatever was Pasted into the 10 cells is now in 200 cells.

How can I be sure that any changes I make to a set of filtered cells are ONLY applied to those cells?

View 4 Replies View Related

Subtract Amount From Cell Until It Reaches 0 Then Move And Subtract From Next Amount?

Apr 28, 2012

I am trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.

AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?

View 6 Replies View Related

Iteration Inconsistency: Allow For A Cost Being Added To Loan Amount Where The Cost Is Based On The Total Loan Amount

Mar 15, 2007

In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001

On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?

View 5 Replies View Related

UserForm Initialization: Fill The Form Out Once And Click 'OK' (run The Code To Put The Form Data Into A Sheet)

Mar 31, 2009

I'm missing something in my UserForm initialization code. If I fill the form out once and click 'OK' (run the code to put the form data into a sheet), when I go back into the form all the old info is still there. If I then click 'Cancel' (Unload Me) and reopen the form, the old data is cleared out. What am I missing to make it clear it out the first time?

View 2 Replies View Related

Lookup/Fill-in Form ? (insert Data Fields Into A Spreadsheet Form)

Dec 2, 2008

I'm having trouble trying to come up with a way to insert data fields into a spreadsheet form. I have a travel authorization form that I would like to have automatically fill in the required fields based on typing in a name. i.e. I would type in an employees name, and it would automatically fill-in the correct address, etc for that employee. I have attached a spreadsheet that contains one sheet as the form, and another sheet containing the employee data. I know nothing about VBA, but I have a feeling that is where I need to go.

View 3 Replies View Related

Recall Sub In A Form To Pull Back In Data To A Form When Reference Added

Jun 26, 2013

I am needing to create a form that exports data (a quote) to an Excel Db (table) and is then able to recall the data back into the form. (the default form in excel does this and I want to copy that.)

Once the data is called back in, I can then export it to another Table to show that the quote has been approved and will be used.

I am having trouble with the VBA coding that copies the inputted quote in Cell C2 (the reference for the quote number) of the "Form" sheet and looks it up in the "Database" sheet. I have tried several variations of code, but nothing works so far.

-SS
Sub RecallQuote()
'
' RecallQuote Macro
'
Sheets("Form").Select
Range("C2").Select 'this is the cell that holds the quote number to look up from the table

[Code] ......

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved