Deploying custom toolbar and custom icons
I'm working on a custom toolbar for a workbook I'm building (plenty of
buttons, including custom icons). We've decided to stick the workbook on the
network and have other folks access it as well. What is the preferred method
for ensuring the toolbar is available for all users when they open the
workbook?
It's been years since I had to deploy a custom toolbar, and that was in
Word97 via a template- whereas this is in Excel2003, and it is an active
workbook (not a template). I googled and found a lot about building a custom
toolbar via code, but there were references to issues with custom icons, and
I'm hoping there is a way to just keep the toolbar connected to the workbook
without having to generate it via code for each user.
Thanks for any advice,
Keith Tag: Togglebutton Tag: 997534
Populate Deposit Record from another worksheet
I'm way over my head here!
I have about 50 worksheets. Part of every sheet is a simple payment record.
Every worksheet is the set up exactly alike. In all worksheets D6 = FName,
N6=LName, D54:D95=DatePaid,G54:G95=Tuition, J54:J95=Fee, K54:K95=Gift.
I would like to populate a deposit record on another worksheet
(DepositRecord) with the information above. Each payment would go on the
next empty row in the deposit record. On deposit day a total amount would be
calculated on the DepositRecord (this would be done manually). After the
deposit is made, new records would be added below for the next deposit day.
Hope this is clear enough to get me started Tag: Togglebutton Tag: 997533
Reading field values selected by a Page variable
I have a pivottable with row, column (District) and page (Region) variables.
There are 80 districts available when Region is set to ALL, but only a few
for each Region.
I need Basic code that can read the District values displayed to the user
depending on the Region selected. The District field is already set to
ascending sorting. The code:
Set fld =
AllDistrictsCalc.PivotTables("PivotTable5").VisibleFields("District1")
NumDistricts = fld.PivotItems.Count
always returns "80". The code:
For iItem = 1 To NumDistricts
flag = fld.PivotItems(iItem).Visible
Next iItem
tells me all values are visible.
Can anyone point me in the right direction? Am I simply using the wrong
Property? Tag: Togglebutton Tag: 997520
Run-time error 1004 since upgrading from Excel 2003 to 2007.
I have a workbook with many, many macros. Since I upgraded to Excel 2007, I
am receiving the following error message with one of my macros:
"Run-time error 1004. The number must be between 1 and 32767. Try again by
entering a number in this range"
Here is a sample of the code. The statement "Application.MaxIterations =
olditer" is the line that is being highliged by Visual Basic.
skipcalc4:
If Worksheets("Workarea").Cells(43, 7) < 0 Then
Worksheets("NPV Model Workarea").Cells(43, 7) = 0
End If
Application.MaxIterations = olditer
Application.MaxChange = oldchange
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Worksheets("Workarea").Visible = False
End Sub Tag: Togglebutton Tag: 997514
Radio Button Size and Print Features
Is there a way to resize the 'Control' radio button? It is rather small (as
compared with the 'Form' radio button).
Also, the 'Control' radio button - when printed - is surrounded by a dark
box ... ugly ... is there any way to change or remove this feature?
I like the functionality of the 'Control' button, but the 'Forms' button is
much more 'GUI and printer friendly' ... in my opinion.
Thanks. Tag: Togglebutton Tag: 997513
Fill down two columns...C & D
The following code works, but need it to work for not column C&D.
Any clues
Sub Fill_down_C()
Application.DisplayAlerts = False
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long
Set wks = ActiveSheet
With wks
Col = .Range("C1").Column
Set Rng = .UsedRange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Rng Is Nothing Then
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If
With .Cells(1, Col).EntireColumn
.Value = .Value
End With
End With
Application.DisplayAlerts = True
End Sub Tag: Togglebutton Tag: 997508
Distinguishing 0 formatted as "na" and as 0
With this standard format, a zero will appear as as 0:
#,##0;-#,##0
However, you can set zero to appear as "na" using the following
format:
#,##0;-#,##0;"na"
My question is whether it is possible to test whether the cell is
showing a 0 as "na", even though the underlying value is still a 0.
For example, I cannot use IF(A10="na",... because the cell A10
actually still contains a 0; it just looks like "na" because of the
formatting. Tag: Togglebutton Tag: 997502
Set StartRange and End Range
I am trying to get Excel to select all the cells between Cell A3 and the last
used cell in Column A.
Code below:
Dim StartCell, EndCell As Range
Set StartCell = Range("A3")
Set EndCell = Cells(Rows.Count, "A").End(xlUp)
Range("StartCell:EndCell").Select
What am I doing wrong?
Thanks,
Ryan---
--
RyGuy Tag: Togglebutton Tag: 997497
Error using..... .Value = .Value
Running the following two codes...
About halfway running through each page in my workbook, it stops and
gives me an error message on the following line. Any clues as to why
it completes this function on about 15 sheets, then stops halfway with
an error?
Is there a better solution? (Should I combine both columns to fill
simultaneously, change the line to another code, ???)
.Value = .Value
Sub Fill_down_C()
Application.DisplayAlerts = False
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long
Set wks = ActiveSheet
With wks
Col = .Range("C1").Column
Set Rng = .UsedRange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Rng Is Nothing Then
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If
With .Cells(1, Col).EntireColumn
.Value = .Value
End With
End With
Application.DisplayAlerts = True
End Sub
Sub Fill_down_D()
Application.DisplayAlerts = False
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long
Set wks = ActiveSheet
With wks
Col = .Range("D1").Column
Set Rng = .UsedRange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Rng Is Nothing Then
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If
With .Cells(1, Col).EntireColumn
.Value = .Value
End With
End With
Application.DisplayAlerts = True
End Sub Tag: Togglebutton Tag: 997495
How to return a column number?
I have a large database that continues to grow on a weekly basis. Within
this database, I have formula references using vlookup, but I keep having to
adjust the col_index_num for these cells because the database keeps
expanding. Is there a function I can use to specify the right-most column in
the database instead of using a fixed column for vlookup? Ie,
"farthest-right" instead of "M", so if the database extends through columns
AK or LAA or beyond it always returns the column label of that right-most
data column? Tag: Togglebutton Tag: 997489
Setting Range = Array of User-Defined Types
I have a large array ['P()'] of Public Types ['PORecord'] from which I would
like to write some of the sub-types [eg. 'PO_OutputDate'] to a spreadsheet at
the end of a set of calculations. Currently I am using a "For...Next" loop,
but this is slooww.
Currently my Code goes something like this:
'Start Code
For q = UBound(P, 1) To LBound(P, 1)
Sheet1. Range(Info.PO_Output_Col & q).Value = P(q).PO_OutputDate
Next q
'End Code
What I would like to do is write all the OutputDates in the array at once to
a range rather than stepping through it bit by bit. This is what I had in
mind...
'start code
Sheet1.Range("Z:Z") = P().PO_OutputDate
'end code
... however I get an "invalid qualifier" error. What am I doing wrong? How
can I fix this?
Thanks in advance,
Steve Tag: Togglebutton Tag: 997487
Use VBA to Enter Break-Mode?
I run a lot of code on a remote computer. The problem is that sometimes the
code hangs up, or the job runs longer than anticipated and I need to break
the code. unfortunately, sometimes I can't even remote into the machine or
Ctrl-Break isn't recognized.
I played with setting up a watch that breaks the script if a file named
pause.excel appears at a specific location - this did allow me to force the
code to enter break mode on a remote computer by creating the file
pause.excel from my laptop.
I haven't played with the watch feature until now so to test it I savd the
file, launched from another computer and looked at the watch, but it was
empty.
Is there VBA that I can put in the On-open statement that would Add my
critera for a Watch?
Or is there another way to force Excel into break-mode from a remote computer?
--
"Trying to make reports so easy... even a monkey could run ''em!" Tag: Togglebutton Tag: 997486
Userform input question
Hi,
Is there a way to 'force' a user to input the time on my userform in the
format xx:xx, ie 24hrs with ':' between the hours and minutes
for example......
12:23 is good
09:12 is good
8:15 is good
1223 is bad
0912 is bad
815 is bad
the txtbox for this on the userform are txttime and txtATA
if the wrong format is used I would ideally like a msgbox to appear to
advise wrong format used.
hope you can help
thanks Tag: Togglebutton Tag: 997481
Change "With Worksheets" to "Activesheet"
Code works perfectly....
But plan on using this on various sheets.
How do i change this line to "active sheet"?
With Worksheets("sheet 1")
Sub GetFirstFourLetters()
Dim Col As Variant
Dim X As Long
Dim Z As Long
Dim LastRow As Long
With Worksheets("s.07")
For Each Col In Array("A", "B")
LastRow = .Cells(Rows.Count, Col).End(xlUp).Row
For X = 1 To LastRow
For Z = 1 To Len(.Cells(X, Col).Value)
With .Cells(X, Col)
If Mid$(.Value, Z, 1) Like "[A-Za-z]" Then
.Offset(0, 2).Value = Mid$(.Value, Z, 4)
Exit For
End If
End With
Next
Next
Next
End With
End Sub
Thanx Tag: Togglebutton Tag: 997479
VBA Email Cell Contents When Filled In
Good morning. I have created the following spreadsheet to log requests>
A (email address)
B ("yes")
C (Date of Request)
D (Time of Request)
E (Description of Request)
F (Request Due)
This will be on ongoing log. I would like for when a new row is completed,
that a "confirmation of request" email go out to the email address listed. I
tried some things by looking at different posts and I am still getting
errors. Any help would be greatly appreciated.
This is the code that I currently have that is not working. Please let me
know if I am way off.
.To = cell.Value
.Subject = "Request Confirmation :" & " " & _
Cells(cell.Row, "E").Value & " " & _
Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
" Please confirm the following report request." & vbNewLine & vbNewLine & _
" Detail are : " & vbNewLine & vbNewLine & _
" Date of Request : " & Cells(cell.Row, "C").Value & vbNewLine & _
" Time of Request : " & Cells(cell.Row, "D").Value & vbNewLine & _
" Description : " & Cells(cell.Row, "E").Value & vbNewLine & _
" Deadline : " & Cells(cell.Row, "F").Value Tag: Togglebutton Tag: 997475
printing same tab from multiple workbooks
I currently have numerous workbooks (one for each employee) containing thier
timesheets for the current year (file name: "[NAME] TimeSheet's 2008.xls").
the tabs in these workbooks are simply named by the week ending date (every
saterday).
I would like to have a special workbook that's only perpose is to batch
print all or certain timesheets.
In this wkb, i would have a cell in which i type the week ending date that i
want to print. I am thinking it could search all files in a folder for that
tab name and print them OR furthurmore, I would also like to have a list of
employees names that I could check off, or something, if i want the timesheet
for that person printed.
would any of this be possible?
Thanks Tag: Togglebutton Tag: 997472
SQL Query to Excel
I am in the initial throws of attempting to populate an Excel Worksheet, with
table rows selected from a SQL Server DB, using the following code:
Sub WSPopulate()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strWhere As String
Set con = New ADODB.Connection
con.Open "Driver={SQL Server};Server=GVS00534\node1;Database=mydb;"
strWhere = "SELECT * FROM dbo.application WHERE Owner_L2 = 'sbcorg'"
Set rs = con.Execute(strWhere, , 1)
Range("a1").CopyFromRecordset rs
rs.Close
con.Close
End Sub
This worked VERY nicely the first time...and ALL my data rows/columns
populated the page. However, on my second attempt, only 3 columns from the
sql db passed to the Worksheet, and on my 3rd, nothing. On the third try,
the hour glass hardly flickered...it's as if I have consumed all of some
resource along the way. Do I have a "hygiene" issue here, where I am not
releasing something I should be? Any other ideas? Also, does anyone have a
pointer to tips on how to do this? I am pretty new to DB access from excel
VBA, and some "examples" might help me a lot.
Thanks,
Patk Tag: Togglebutton Tag: 997471
Redimming (preserve) a 3D array
This is a repost of an earlier question that didn't recieve responses (my
question may have been too obscure).
I have a 3D array which I'll call myarray(1 to 10, 1 to 100, 1 to 3)
I would like to reduce the number of parameters to turn it into a 2D array
which I'll call mynewarray(1 to 10, 1 to100) while keeping the data that is
already in the array.
Redim/preserve only allows you to mess with the last dimension (1 to 3),
which is fine- but from help:
"Similarly, when you use Preserve, you can change the size of the array only
by changing the upper bound; changing the lower bound causes an error"
The data I'd like to preserve is in the middle, e.g. mynewarray(x, y) =
myarray(x,y,2)
Is there a better method, or will I be forced to loop through and build
mynewarray one value at a time? The real problem is that in my larger loop
of code, myarray is dynamic and is updated frequently, and I'll have to pull
current copies of mynewarray fairly often, and that seems like a lot of
processing overhead. If there is a way to redim the array directly, that
would save me all kinds of time.
Thanks!
Keith Tag: Togglebutton Tag: 997470
Change NumberFormat and Cell Fill Colour in SELECT CASE Construct
Hi,
I'm using the Select Case construct for conditional formatting on my
spreadsheet. The data is live linked to a SharePoint table, so when updated,
is pulled through in Text format. Within this data there may be some dates,
which are shown as Text in the format "dd/mm/yyyy" when pulled through. I
need to turn these into Excel dates (ie 39652, formatted as 'Date') and
format these cells according to whether or not they are prior to TODAY().
I'm not a VB coder - most of the Case constructs have been adapted from
online example. Here's what i've written for this particular Case:
For Each Cell In Rng1
Select Case Cell.Value
Case "##/##/####"
Cell.NumberFormat = "dd/mm/yyyy"
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
If Cell.Value < TODAY() Then
Cell.Interior.ColorIndex = 4 'Green
End If
End Select
Next
I think there are several things that could be wrong here, mainly
'##/##/####' and Cell.Value < TODAY(), but no idea how to correct. Any
advice, or an easier way to do this automatically would be appreciated. Tag: Togglebutton Tag: 997469
Gathering data from multiple, non-contiguous ranges
Hello, Everyone!
I know this question has been asked many times and dealt with in many ways,
but I don't know how to modify it for my personal dilemma. I found this code
in another forum for copying a single range from all sheets of all books in a
folder to a single new master sheet.
[CODE]Sub Open_All_Files2()
Dim oWbk As Workbook
Dim w As Worksheet
Dim sFil As String
Dim sPath As String
Dim k As Long, n As Long
sPath = "C:\Documents and Settings\gl1b\Desktop\Tacoma 2007\"
ChDir sPath
sFil = Dir("*.xls") 'change or add formats
Application.DisplayAlerts = False
k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row
n = k + 1
Do While sFil <> ""
k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row
n = k + 1
Set oWbk = Workbooks.Open(sPath & "\" & sFil)
Range("A13:J27").Copy
Set w = ThisWorkbook.Sheets(1)
ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial
oWbk.Close True
sFil = Dir
Loop
Application.DisplayAlerts = True
End Sub[/CODE]
I ran it and it seems to open, copy and close everything in the folder I
specified. I just don't see the pasted data anywhere.
My specifics are these:
I need to copy everything from B4 to B?? <-- wherever the data in col B runs
out, C4 to C?? and N4 to N??, paste it continuously down Cols A, B and C of a
new master sheet.
I have puzzled over this code for 2 days, changing things and changing them
back when it didn't work. If anyone could make the necessary tweaks, I would
appreciate it tremendously.
Thanks for your time.
Arlen Tag: Togglebutton Tag: 997467
Like operator in SQL with Excel Driver
Hi,
I am using adodb recorset and excel driver to query a worksheet in
excel file. When I try to use like poerator it gives me an error. Can
someone please help me. Below is my code
When I remove % and replace it with correct part number it shows the
correct results. But my part number column has more part number and I
just need to count records that contains part number I supplied.
Sub PartNumberUsage()
Dim condb As New ADODB.Connection
wk_selected = ActiveSheet.Name
wk_xtab = "MSO_Xtab"
dbpath = ActiveWorkbook.FullName
conn = "DRIVER={Microsoft Excel Driver (*.xls)}; ReadOnly=1;DBQ=" &
dbpath
condb.Open conn
Set rst = New ADODB.Recordset
Sql = "select count(" & Worksheets(wk_xtab).Range("A1").Value & ")
from [MSO_Xtab$A:iv] where Part_no = '301971%' "
Call rst.Open(Sql, condb, CursorTypeEnum.adOpenForwardOnly,
LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)
Cells(ActiveCell.Row, ActiveCell.Column + 1).Value = rst(0)
Application.StatusBar = False
rst.Close
Set rst = Nothing
Set condb = Nothing Tag: Togglebutton Tag: 997465
Problem with Userform the second time
I have a macro in my personal.xls attached to a toolbar that opens a userform
and displays various files in a listbox along with a date in a textbox. I can
select a file and date and then run a command (code below) button to open
that file. It works the first time but when I open the userform the next time
and select a file I get the following error
"Could not complete the operation due to error 800a01a8". I close the
userform and it works the next time.
Basically it does not work after each time I press the command button - i
get error above, close the userform, open it again, it them works next time
and we start the loop again
What I can see is when it does not work the command button looks like it has
focus but also the listbox has the file highlighted I opened the previous
time. When it does works the command button has no focus and nothing is
highlighted in the listbox and the cursor is flashing in the text box
any ideas what problem is and how to fix
Private Sub CommandButton1_Click()
Dim wbOpenFile As Workbook
ActiveSheet.Calculate
Set wbOpenFile = ActiveWorkbook
Dim PLpath As String
PLpath = Range("PLpath")
Workbooks.Open Filename:=PLpath
With UserForm1
.Hide
End With
wbOpenFile.Close savechanges = False
--
Kevin Tag: Togglebutton Tag: 997464
Copy the value to the next open cell when the original cell changes
I have a Excel spreadsheet that is linked to another program and
displaying a value from the linked db in the first cell of the
spreadsheet. What I would like to do is capture the value of the first
cell as it changes and copy to the next available empty cell in the
row. Something like this example below.
Initial value shows:
Col1 Col2 Col3
53
when the value changes the first time do this:
Col1 Col2 Col3
54 53
when the value changes again do this:
Col1 Col2 Col3
55 53 54
And so on.....
Any help would be greatly appreciated. Tag: Togglebutton Tag: 997461
move data from rows to columns
Is there a way to combine rows of data where the company, address, name, and
other columns are the same with a product description, product code and qty
that contains different data. Some companies have numerous entries for each
product ordered, other companies may only have one row. THe size of the spead
sheet also varies.
DAGRT PROGRAM DESCRIPTION ECP 10 DIG CORP COMPANY
NAME ADD1 STATE_ ZIP FIRST_NAME LAST_NAME ITEM # ITEM DESCRIPTION ORDER
UOM SHIPPED QTY
1410631 ALLIED BUSINESS 74121594 350081 W G
LEFFELMAN & SONS INC 340 N METCALF AVE IL 61310-1119 Doug O'Rourke BLW2 W-2
BLANK FACE & BACK LASER 1=1PK EA 11
1410433 AMERINET 72004581 334523 SPECIALIZED
MEDICAL MGMT 4900 BARRANCA PKWY STE
103 CA 92604-8603 Melinda S LMISCRECST 1099 MISC ST COPY LASER 1=1PK PK 2
1410433 AMERINET 72004581 334523 SPECIALIZED
MEDICAL MGMT 4900 BARRANCA PKWY STE
103 CA 92604-8603 Melinda S 7956E 7956E/DW ENV FOR 1099
R,MISC,DIV,B/MW396 EA 1400
1410433 AMERINET 72004581 334523 SPECIALIZED
MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S L1096NC FREE
LASER 1096 TRANSMITTAL 1EA EA 4
1410433 AMERINET 72004581 334523 SPECIALIZED
MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LMISCFED 1099
MISC COPY A LASER 1=1PK PK 2
1410433 AMERINET 72004581 334523 SPECIALIZED
MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LMISCREC 1099
MISC COPY B LASER 1=1PK PK 2
1410433 AMERINET 72004581 334523 SPECIALIZED
MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LRFEDA 1099 R
COPY A LASER 1=1 PK PK 1
1410433 AMERINET 72004581 334523 SPECIALIZED
MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LRPAY 1099 R
COPY 1/D LASER 1= 1 PK PK 1
1410433 AMERINET 72004581 334523 SPECIALIZED
MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LRREC2 1099 R
COPY 2 LASER 1=1 PK PK 1
1410433 AMERINET 72004581 334523 SPECIALIZED
MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LRRECB 1099 R
COPY B LASER 1=1PK PK 1
1410433 AMERINET 72004581 334523 SPECIALIZED
MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LRRECC 1099 R
COPY C LASER 1=1 PK PK 1
1410433 AMERINET 72004581 334523 SPECIALIZED
MEDICAL MGMT 4900 BARRANCA PKWY STE 103 CA 92604-8603 Melinda S LMISCPAY 1099
MISC COPY C LASER 1=1PK PK 2
1410634 AUTO/TRUCK PRIDE 71645177 261678 REPUBLIC
DIESEL 305 E COLLEGE ST KY 40203-2395 Priscilla Fields TT3B W-2 EMPLOYEE SET
3 PART NCR 2/PG EA 276
1410634 AUTO/TRUCK PRIDE 71645177 261678 REPUBLIC
DIESEL 305 E COLLEGE ST KY 40203-2395 Priscilla Fields TT4A W-2 EMPLOYER SET
4 PART NCR 2/PG EA 276
1410634 AUTO/TRUCK PRIDE 74139630 499947 PRECISION
BRAKE & WHEEL 15 E BELLEVIEW AVE CA 93257-2441 Joy Kanthack LW24 W-2 4 PART
KIT LASER 50/PK EA 1
The above is a sample of the data in question. I need each company to have
one row that contains all the items ordered. Tag: Togglebutton Tag: 997459
Build Pivot Table with VBA; Keeps Throwing an Error
I keep getting Run-Time Error 5
Invalid Procedure call or argument
The following is hilighted yellow:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("C2_UnionQuery").Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:="[C2_UnionQuery.xls]Summary!R3C1",
TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
I can't figure out what is causing the error...
All data resides on this sheet:
C2_UnionQuery
I am trying to put the Pivot Table on this sheet:
Summary
My code is below:
Sub BuildPT()
Application.ScreenUpdating = False
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = "Summary" Then
Sheets("Summary").Delete
Set ws = Worksheets.Add
'ws.Move After:=Sheets(Sheets.Count)
ws.Name = "Summary"
End If
Next wks
Application.ScreenUpdating = False
Sheets("C2_UnionQuery").Select
Range("A1").Select
'Cells.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("C2_UnionQuery").Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:="[C2_UnionQuery.xls]Summary!R3C1",
TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
Sheets("Summary").Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("RVP")
.Orientation = xlRowField
.Position = 1
End With
'...some more Pivot Fields here...
End Sub
Please help......
Thanks,
Ryan---
--
RyGuy Tag: Togglebutton Tag: 997455
Copy Current Month into YTD Spreadsheet
I would like to create a macro to copy the current month information from one
spreadsheet into a Montly YTD spreadsheet.
I want to be able to create a macro so that for example if it's June, the
macro would know how many columns over it needs to "copy to" on the YTD
spreadsheet. I will be doing this each month.
Hope that makes sense.
Thanks!
--
AJ Tag: Togglebutton Tag: 997443
SheetCalculate Event
Hi all,
The Macro1 below was not evoke upon the calculation of active worksheet. I
put these codes under ThisWorkBook but saved as addin (.xla) file. The
auto-filter was set on the newly created worksheet.
Private Sub Workbook_SheetCalculate(ByVal ob As Objects)
If ob.AutoFilterMode Then Macro1
End Sub
Should these event code must be placed under ThisWorkBook of newly created
worksheet in order to call Macro1? Thanks in advance! Tag: Togglebutton Tag: 997439
Search for strings
does anybody know simple and correct way search for key word (or strings,
like AKL-35, etc) in Excel workbook that have multiple worksheets. Key word
can be in any place inside spreadheets. Without messing with macro ar smth
else, just search, fast and simple. Tag: Togglebutton Tag: 997438
submit web form
hi,
i have the following. i was able to fill the fields in, but i just cant get
the code to simulate clicking the "OK" button to submit the form...
'This project includes references to "Microsoft Internet Controls" and
'"Microsoft HTML Object Library"
'Variable declarations
Dim myIE As New InternetExplorer 'New '
Dim myURL As String
Dim myDoc As HTMLDocument
Dim strSearch As String
'Set starting URL and search string
myURL = "https://cantsay.com/Transactions/Logon.asp"
strSearch = "me"
'Make IE navigate to the URL and make browser visible
myIE.navigate myURL
myIE.Visible = True
'Wait for the page to load
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
'Set IE document into object
Set myDoc = myIE.document
'Enter search string on form
Dim strpassword As String
strpassword = "mine"
With myDoc.getElementsByName("UserName")(0)
.Value = strSearch
End With
With myDoc.getElementsByName("Password")(0)
.Value = strpassword
End With
With myDoc.getElementsByName("Client")(0)
.Value = "TEST"
End With
'Submit form
'myDoc.getElementsByName("Client")(0).OK.submit
'myDoc.forms(0).submit
'myDoc.forms("Logon").submit
'Set sform = myDoc.forms(0)
'sform.submit
'Set formbutton = myDoc.getElementsByName("btnSubmit")
'formbutton.Click
Windows.Application.SendKeys "{ENTER}"
SendKeys (strIn)
SendKeys "{ENTER}"
...
any help would great be appreciated!!!
thanks in advance,
geebee Tag: Togglebutton Tag: 997436
multiple Insert Shift:=xlDown
hi all !!!
i just need to write down my code so to make 300 times the next command..
or to insert 300 times a void line ( dimension e80:l80 ) in e 82 :
aWS.Range("E80:L80").Copy
aWS.Range("E82").Insert Shift:=xlDown
let me know pls if you have any idea !!
byy! Tag: Togglebutton Tag: 997431
Code help
Hi
I have this bit of code that is part of a macro to search for and copy paste
data when a match is found.
wks1.Cells(iRow, 1).EntireRow.Copy Destination _
I don't want the ENTIRE ROW to be copied just the range A:O
can anybody adapt the code for me please to carry this out
thanks Tag: Togglebutton Tag: 997428
Trimming worksheet name from an imported data source
I'm bringing in data from an external source that changes each month, and to
make the macro run correctly it needs to have the same name each month on the
tab (I think. I'm still a novice at VBA). It's name is always the same length
except for the month's name at the beginning. I'm having a little trouble
getting the Trim function to work in VBA though. Can somebody help me with
this? I'm sure I'm missing something easy. If it helps here's the name for
the July/August worksheet: JA_20082008_CHRYSLER_300_SERIES
Next month the JA will be replaced with AS for August/September. I really
would appreciate your help.
--
Message posted via http://www.officekb.com Tag: Togglebutton Tag: 997427
Countif and
I can't seem to come up with the right code to say the following:
Count if $H&1:$H199 = "5th" and if B2 = "Active"
H1:199 will always be the same reference. B2 will change to c3, etc.
Help please
Hank Tag: Togglebutton Tag: 997416
Detect UPPERCASE in cell value?
Fellow programmers... below is some code I tried to create to detect
if the contents of the ActiveCell.Value was all UPPERCASE. The
spreadsheet is importing data and if a cell's value is all updercase
it means something specific with the data that being imported. Any
ideas as to how to make a TRUE & FALSE statement based on the
characters being all uppdercase?
Declare Function IsCharUpper Lib "user32" Alias "IsCharUpperA" _
(ByVal cChar As Byte) As Long
Sub Button22221_Click()
If IsCharUpper(ActiveCell.Value) = True Then
MsgBox "It is bitch.", vbOKOnly, "-Test-"
End If
If IsCharUpper(ActiveCell.Value) = False Then
MsgBox "It's not uppercase.", vbOKOnly, "-Test 2-"
End If
End Sub
Thanks in advance! Tag: Togglebutton Tag: 997411
Last KeyStroke
Is there a way VBA can tell the last keystroke that a user entered? Here is
what I want to do:
If the user hits enter, instead of going down one row, I wish to go across
one column. But if they press the arrow keys, then I don't want to "alter"
their choice.
So if they press the down arrow, they go down one cell, but if they press
the enter key they go right one cell. Ideas? Tag: Togglebutton Tag: 997408
Send e-mail from Excel macro problem
I'm receiving the following error:
Compile error: user-defined type not defined
It's not recognizing this:
Dim objOutlk As New Outlook.Application 'Outlook
I've compared it to others online, and it looks the same, but I must be
missing something?
The code is below. Any help would be much appreicated! Thanks!
Sub SendEmails()
Application.ScreenUpdating = False
Dim Row, Col As Integer
Dim ConfDate, UName, UNumber As String
Row = 2
ConfDate = Application.InputBox("Enter the date attestation should be
received: eg (Friday August 18, 2006")
UName = Application.InputBox("Enter your name as it will appear at the
bottom of the email")
UNumber = Application.InputBox("Enter your phone number as it will appear at
the bottom of the email")
Workbooks.Open Filename:="[server path]\Business Leaders.xls"
Range("A2").Activate
Do
If Cells(Row, 1).Value = "Investigate" Then
MsgBox ("One file found where investigation is required- Name
Investigate.xls")
Else
MsgBox "Click to Send Email"
Dim objOutlk As New Outlook.Application 'Outlook
Dim objMail As MailItem 'Email item
Dim strMsg
Const olMailItem = 0
'Create a new message
Set objOutlk = New Outlook.Application
Set objMail = objOutlk.CreateItem(olMailItem)
objMail.To = Cells(Row, 1).Text
objMail.cc = Cells(Row, 2).Text
objMail.Subject = "User Verification " & " - " & Cells(Row, 1).Text & " " &
Date$
'Add the body
strMsg = Null
strMsg = "Hello " & Cells(Row, 2).Text & vbCrLf & vbCrLf
[etc...] Tag: Togglebutton Tag: 997402
adding automations to Excel (doesn't seem to work properly) !
successfully created an Add-in set called "ExcelAddIn2.MyFunctions"; and
added it.
This was following the book "Visual Studio Tools for Office Using C# with
Excel, Word, Outlook, and InfoPath" by Eric Carter & Eric Lippert (sometimes
called Eric2). See especially pp. 88-89 in the 2006 Pearson Education
edition.
However, when I created a new project, e.g. "ExcelAddIn3.TheseFunctions",
I use the Automation function, scroll down the list, and find it.
I check the appropriate box in the "Add-Ins" dialog box (or list box or
whatever form it is).
However, when I use insert->function -> Or Select a category and scroll
down, it is absent.
I also have found the same true of add-ins I've downloaded from the web.
For these, I store in a known sub-directory, then use Tools->AddIns
("Browse" button), find the appropriate file in the subdirectory.
Once again, I check the appropriate box in the "Add-Ins" dialog box, but
similary when I go to insert->function -> Or Select a category and scroll
down, it also is absent.
I've tried removing the "ExcelAddIn2.MyFunctions" -- thankfully it comes
back when I restore it ; I've tried re-booting and so forth.
Does anyonoe have any idea what the problem is?
Also, how does one remove "Add-Ins available" from the aformentioned Add-Ins
dialog box (obtained by using Tools--> AddIns)?
NOTE: I am using VSTO, Microsoft Visual Studio 2008, and Microsoft Office
(Proffesional Edition 2003) including the Microsoft Excel 2003.
Does anyone know of a link (URL) that would help?
Thread discussion: To do anything at all with VSTO, one needs to be able to
use an add-in in the appropriate office program, e.g. Excel. However, if
this thread belongs elsewhere please let me know where I should put it.
Thanks in advance, (-:
--
Joseph
a/k/a joseph_doggie Tag: Togglebutton Tag: 997401
macro = Can't perform requested requested operation
I was editing a macro. Suddenly said the "Can't perform etc." when tried
to run it in the editor. Backed out without saving and closed
everything. Opened... and it won't run or step into without the same
message.
If I click the button on the spreadsheet to run it it says it can't find
the macro. The macro is listed in the macro section along with other
macros. When I choose this particular macro the run is blanked out.
Baffling... what do I do now?
Thanks
John Tag: Togglebutton Tag: 997398
For Next Loop Function for Creating New Spread sheets
Dear All,
I am having a Master Workbook from which I've to create a new book having
"n" number of sheets.
Let me explain clearly, In my master workbook ,there are 2 sheets , one is
a Spread sheet which is a standard format. I am also having one more sheet in
which totally 5 columns are there. My users will enter the data in those 5
columns (A1:A30, B1:B30.....E1:E30).
I'm creating a command button in the program.
Now I'm creating a VB Coding for the button in such a way that it should
copy the Standard format sheet five times. Data in First column of sheet has
to be copied/pasted to First copy of standard format sheet , Second column
data sheets should be copied/ pasted to second copy of Sandard format sheet
and it will be repaeted upto 5 sheets.
I enterd coding as follow,
For I = 1 to 5
Worksheets("Std Format").Activate
Cells("A1").value = Worksheets("Tech sheets").Cells(1,(I+2))
Cells("A2").value = Worksheets("Tech.sheets").Cells(2,(I+2))
....
...
...
Cells("A30").value = Worksheets("Tech.sheets").Cells(30,(I+2))
Activesheet.Cells.Select
Selection.Copy
Windows (newfile).Activate
Worksheets.add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Windows(myfile).Activate
Next I
Is it the correct way of coding for the above procedure I mentioned?
Please correct me if I am wrong. Also Please suggest me if there is any
other better options for sdoing the above activity.
Hope it is clear. Tag: Togglebutton Tag: 997396
Use of a range as an email address in a .SendMail array
Rather then hard wiring the email addresses into the code I wish to
place it in the worksheet then define it as a Range and input the
range in the array as shown below.
How can I get it to work, I think I need something other than .Value
Dim MyPath5 As String, MyRange5 As Range
Set MyRange5 = MyWBConvertor.ActiveSheet.Range("D1")
MyPath5 = MyWBConvertor.Path
''''''''''''''''''''''''''''''''''''''
MyWBLnkZn.SendMail Recipients:=Array("MyRange5") Tag: Togglebutton Tag: 997388
How could I have saved memory?
I threw together a macro in Word 2007 yesterday, but it kept
interrupting. There was no error, but I'd get the annoying message
box that says "Code execution has been interrupted." and the CONTINUE,
END, and DEBUG buttons. I've learned this is usually an indication
that I'm stretching the capacity of my memory - all I have to do is
hit CONTINUE and it runs along just fine - until it interuupts again!!
I had tables in a Word doc with reprot numbers in one column, and
several more blank columns. The data to fill in those blanks was
mostly in an Excel spreadsheet. One blank, though, could only be
filled in by finding the report document and grabbing one line of
text.
So the macro was designed to set an object to the table I had selected
and iterate down the report numbers, open the spreadsheet and find the
needed values for that report, then open the report, grab the one line
of text, and close the report. Lather, rinse, repeat until the end of
the table.
Can someone see where I could have saved memory overhead and made this
run a bit smoother?
Ed
Sub EnterMyInfo()
Dim doc As Document
Dim tbl As Table
Dim str As String
Dim cll As Word.Cell
Dim tir As Document
Dim this As Range
Dim oXL As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim xlcll As Excel.Range
Dim x As Long, y As Long
Dim a As Long, b As Long
Dim vlu
Set doc = ActiveDocument
Set tbl = Selection.Tables(1)
Set oXL = New Excel.Application
Set wkb = oXL.Workbooks.Open("C:\MyFile.xls")
oXL.Visible = True
Set wks = wkb.Worksheets("Sheet1")
a = wks.Range("A20000").End(xlUp).Row
x = tbl.Rows.Count
For y = 1 To x
Application.StatusBar = "Row " & y & " of " & x
Set cll = tbl.Cell(y, 1)
If Left(cll.Range.Text, 5) = "L5-BB" Then
str = Left(cll.Range.Text, 10)
For b = 2 To a
If wks.Range("B" & b).Value = str Then Exit For
Next b
'Stop
'**********
'vlu was deliberately left undefined
'so I could adjust it depending on the table.
'**********
vlu = Format(wks.Cells(b, 3).Value, "mm/dd/yyyy")
tbl.Cell(y, 2).Range.Text = vlu
vlu = Format(wks.Cells(b, 5).Value, "####0.0")
tbl.Cell(y, 3).Range.Text = vlu
'tbl.Cell(y, 4).Range.Text = wks.Cells(b, 7).Value
Set tir = Word.Application.Documents.Open(FileName:="\\Server1\ &
str & ".doc")
tir.PageSetup.LeftMargin = InchesToPoints(0.75)
tir.PageSetup.RightMargin = InchesToPoints(0.75)
Set this = tir.Content
With this.Find
.Text = "|90. "
.Execute
this.Collapse wdCollapseEnd
this.MoveEndUntil "|", wdForward
tbl.Cell(y, 4).Range.Text = Trim(this.Text)
End With
'Stop
tir.Close wdDoNotSaveChanges
Set tir = Nothing
End If
Next y
EndMeNow:
On Error Resume Next
wkb.Close
oXL.Quit
Set oXL = Nothing
On Error GoTo 0
MsgBox "I'm done!"
End Sub Tag: Togglebutton Tag: 997387
Show UserForm ScrollBar
Hi Everyone,
I developed a UserForm, and I put one PictureBox in it,
the picture size is Hight: 750, and Width: 550, I adjust the Userform
for same size.
the problem is this when I load the UserForm, the picture is not fit
on the screen, I tried to fix the Scrollbars for UserForm but from the
Form property I could not setup it, because I dont know how to do fix.
Please guide me how to put Vertical and Horizontical Scrollbars on the
UserForm to scroll long UserForm.
With best regard.
Shahzad Zafar
Madinah Tag: Togglebutton Tag: 997384
VBA Help
I'm new to VBA and I wanted to know what would be the easiest way to learn
how to write the code. Helpful links??? Thanks in advance. Tag: Togglebutton Tag: 997381
Status Progress Bar
I found the code below in this forum and would like to try it but I'm having
some difficulty getting it to work.
Suppose I have a spreadsheet with a 100 lines of data and Im doing 'stuff'
to each line. I'd like to have the StatusProgressBar increase as each line
is processed.
I'm guessing:
lCounter = the row being processed
lMax = 100 (for 100 lines)
lInterval = 1 (since I want to change it each time a new row is processed)
I'm not sure of:
(1) what "strText" should be or how it should appear in the code
(2) how to call this code in the code that is processing the 100 lines
Any examples or suggestions on how to make this work would be greatly
appreciated. Thanks for the help........
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub StatusProgressBar(lCounter As Long, lMax As Long, lInterval As Long,
Optional strText As String)
Dim lStripes As Long
If lCounter Mod lInterval = 0 Or lCounter = lMax Then
lStripes = Round((lCounter / lMax) * 100, 0)
Application.StatusBar = strText & String(lStripes, "|") & String(100 -
lStripes, ".") & "|"
End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--
JT Tag: Togglebutton Tag: 997377
From Outlook to Excel with VBA
Hi,
I need to get data from message in Outlook to fill fields within Excel
worksheet using VBA. Can anyone suggest me a website or ebook where i can
obtain information?
Thanks in advance
HBruno Tag: Togglebutton Tag: 997376
Never Looped before
I have a range of about 6000 rows, 33 columns (starting at column D going to
column AJ).
The issue. Starting at row 5 I want to place a "loop" to conditionally
format row 6 based on the value in row 5. I know that the conditional format
would look like this:
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=D6<>D5"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.AutoFill Destination:=Range("D6:AJ6"), Type:=xlFillFormats
Range("D6:AJ6").Select
but I need to repeat this for every row until it reaches the end of the
pivot table. I was copying and pasting fomats but excel didnt like that for
some reason.
I'm hoping an automatic code can be written into this sheet so that it does
it automattically...that would be helpful.
Thank you in advance and please let me know if you need ANY further
information. Tag: Togglebutton Tag: 997372
MACRO: Multiple Find / Replace
I want a macro that searches a worksheet for specific strings and replaces
these with another.
In detail:
Search on worksheet (whole worksheet, i.e. lots of rows)
Find: "CAR" and replace with "MIS-CAR"
AND
Find: "BAG" and replace with "MIS-BAG"
AND
etc etc
I need about 20 such FIND-REPLACE in this same macro.
Can you help me, since I got no clue of VBA ???
Thanks Tag: Togglebutton Tag: 997364
Grayed out or disable?
Is there a way to gray out or disable OptionButtons or CheckBoxes in a user
form if a condition is met, either on the spreadsheet (preferred if possible,
since I have conditions set up there already) or prior user form selections?
If so, how would you go about setting that up? Thanks for any help on this
in advance. Tag: Togglebutton Tag: 997360