Drag and Drop
I am trying to learn how to drag an item from one textbox to another textbox.
I copied this code for the help menu:
Private Sub ListBox2_BeforeDragOver(ByVal Cancel As _
MSForms.ReturnBoolean, ByVal Data As _
MSForms.DataObject, ByVal X As Single, _
ByVal Y As Single, ByVal DragState As Long, _
ByVal Effect As MSForms.ReturnEffect, _
ByVal Shift As Integer)
Cancel = True
Effect = 1
End Sub
Private Sub ListBox2_BeforeDropOrPaste(ByVal _
Cancel As MSForms.ReturnBoolean, _
ByVal Action As Long, ByVal Data As _
MSForms.DataObject, ByVal X As Single, _
ByVal Y As Single, ByVal Effect As _
MSForms.ReturnEffect, ByVal Shift As Integer)
Cancel = True
Effect = 1
ListBox2.AddItem Data.GetText
End Sub
Private Sub ListBox1_MouseMove(ByVal Button As _
Integer, ByVal Shift As Integer, ByVal X As _
Single, ByVal Y As Single)
Dim MyDataObject As DataObject
If Button = 1 Then
Set MyDataObject = New DataObject
Dim Effect As Integer
MyDataObject.SetText ListBox1.Value
Effect = MyDataObject.StartDrag
End If
End Sub
Private Sub ListBox2_Change()
End Sub
Private Sub UserForm_Initialize()
For i = 1 To 10
ListBox1.AddItem "Choice " _
& (ListBox1.ListCount + 1)
Next i
End Sub
Private Sub ListBox2_MouseMove(ByVal Button As _
Integer, ByVal Shift As Integer, ByVal X As _
Single, ByVal Y As Single)
Dim MyDataObject As DataObject
If Button = 1 Then
Set MyDataObject = New DataObject
Dim Effect As Integer
MyDataObject.SetText ListBox1.Value
Effect = MyDataObject.StartDrag
End If
End Sub
How do I get it to remove the selected item from the first textbox after it
is added to the second textbox?
Thanks Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981444
need VBA to clear dependent data validation lists in several rows
I'm on xl '03.
I am creating an order form that utilizes several dependent data validation
lists (aligned in a row) to narrow down what product at what price, etc. I
have 15 rows, so the user could order up to 15 products.
They begin by selecting an item in column D. and the rest goes from there.
Obviously, if they change the item in Column D, the other columns do not
automatically clear. I need the active row to clear itself each time the user
changes the item.
I can accomplish this with a recorder macro or worksheet_change(ByVal Target
As Rage) for one row, but I can not figure out how to get this same principle
to apply to all 15 rows.
IN ADDITION:
I need to allow the user the option to select "Misc" in column D and type in
their own product information and pricing (which will erase my formulas in
those columns).
If they change their mind and want to select another item in column d, the
dependent validation lists and vlookup formulas need to be reinstated
somehow.
Is that even possible? Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981434
Using a variable in a formula
Hi
I have a "Contacts" workbook with 12 worksheets in it, one for each month.
I also have a "Vendor" worksheet, also with 12 worksheets in it. I need to
change the formula in cell AA1 of the Vendor worksheet Jan 08 to read the
info in cell A1 on the Jan 08 Contacts worksheet. For the next month, the
formula needs to move to the Feb 08 sheets, and so on.
How can I use the sheetname variable "nws" in a formula to reference the
sheet and enter in the sheetname at that spot?
Here is a bit of the code that I have for this part:
Dim nws as string
For Each x In Worksheets
x.Activate
nws = ActiveSheet.Name
Range("AA1:AA1").Select
ActiveCell.Formula = "='F:\[Contacts 2008.xls]nws'!$A1"
Next x
Thank you
Fred Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981424
Listbox
I have four columns A:D. A=date, b=payee, c=catagory and d=amt. I am trying
to create a listbox based upon the catagory. In other words I want to know
all payments that have been for "entertainment". I have been trying some
code I found from Dave P. as follows;
Option Explicit
Private Sub UserForm_Initialize()
Dim myCell As Range
Dim myRng As Range
Dim myWord As String
myWord = " Entertainment"
With Worksheets("SortSheet")
Set myRng = .Range("d1", .Cells(.Rows.Count, "d").End(xlUp))
End With
For Each myCell In myRng.Cells
If LCase(Left(myCell.Value, Len(myWord))) = LCase(myWord) Then
Me.ListBox1.AddItem myCell.Value
End If
Next myCell
End Sub
It returns "Entertianment" from the category column for each entry. I also
need for it to include the date, payee and amount.
The second question is how can I make the catagory a variable that is
selected by clicking on a cell with the the category name... such as gas,
supplies, etc so that it will show a list of all entries (without a routine
for each).
Thanks for any help
Ronbo Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981408
compare 2columes of name
Please is there a way I can compare two columns of names and getting a true
or false response in the third column, the downside is that the names in each
column are a combination of first, middle and last names that have been
concatenated together (These comparison are in thousands). Help please. thanks
E.G
column 1 column 2
John M James Doug S Smith
Mike j john kate Johnson
Hans Kible Lilly T Hanson Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981403
Do not execute Double Click Event when UserForm is Showing
Is there a way to not execute the Event below if any UserForm is showing?
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If a UserForm is showing Exit Sub Else Continue with code..
End Sub Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981396
Macro to Insert Object with browsing ability
Hello all,
I have, unfortunately, entered the realm of VBA limbo, where I
understand how code works by reading it, but have no idea how to write
it (having just picked things up over time).
What I am trying to do is add functionality into my workbook where a
user can click a button to insert an object, much like you would do
via the Insert --> Object menu option.
My current thought was to use Jim Rech's 'Browse for Folder' in
combination with a simple recorded Macro. Basically, the user should
click the button, be prompted with an ability to select the file to
insert, and then have that location of the file be returned back to
the VBA code so that it can go ahead and insert it.
I can work the macro's independently - Jim's will browse for a file,
but only returns a msgbox. My simple recorded macro will insert an
object fine, but only from a static - hardcoded filename. I'd
obviously like to replace the embedded filename with the prompting
from Jim's code so that rather than return a msgbox, it returns the
filename to my own macro and voila, object inserted.
If there is a better method I am very open to it.
Thanks!
Jim's BrowseForFolder (minus all the background info obviously):
Sub BrowseFolders()
Dim RetStr As String, Flags As Long, DoCenter As Boolean
Flags = BIF_RETURNONLYFSDIRS + BIF_BROWSEINCLUDEFILES +
BIF_NEWDIALOGSTYLE
With Sheet1
DoCenter = True
End With
RetStr = GetDirectory(CurDir, Flags, DoCenter, "Please select a
location to store data files")
If RetStr <> "" Then MsgBox RetStr
End Sub
My easy recorded Macro, would like to replace the "c:\...." with what
comes from Jim's, but can't figure it out:
Sub InsertObject()
ActiveSheet.OLEObjects.Add(Filename:= _
"c:\files\file.abc", Link:=True, _
DisplayAsIcon:=True, IconFileName:= _
"C:\WINDOWS\Installer
\{90110409-6000-11D3-8CFE-0150048383C9}\xlicons.exe", _
IconIndex:=0, IconLabel:= _
"c:\files\file.abc").Select
End Sub Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981390
Change Excel query with a inputbox
I have a Query running in Excel at it works perfectly, I need to add a
popup box for the user to enter a date so they don't have to enter MS
query but can stay in Excel.
Now the data I'm pulling is from a .SQL database and I have a date
field that will change each time someone runs the file Example:
2008-05-02.
I have tried to add a inputbox but the data is not returned when I
enter the date in the input box
Any help would be greatly appreiated. I have included my simple query
below.
Range("L3").Select
With ActiveSheet.QueryTables.Add(Connection:=3D _
"ODBC;DRIVER=3DSQL
Server;SERVER=3DHidden;UID=3Dhidden;PWD=3Dhidden);APP=3DMicrosoft=AE
Query;WSID=3DHidden" _
, Destination:=3DRange("L3"))
.CommandText =3D Array( _
"SELECT ae_dt1.field1, ae_dt1.field2, ae_dt1.field3" & Chr(13)
& "" & Chr(10) & _
"FROM Xtender.sysop.ae_dt1 ae_dt1" & Chr(13) & "" & Chr(10) &
_
"WHERE (ae_dt1.field4=3D'2008-05-02') AND
(ae_dt1.field7=3D'CLINTON')")
.Name =3D "Query from Roc_Checker_1"
.FieldNames =3D False
.RowNumbers =3D False
.FillAdjacentFormulas =3D False
.PreserveFormatting =3D True
.RefreshOnFileOpen =3D False
.BackgroundQuery =3D True
.RefreshStyle =3D xlOverwriteCells
.SavePassword =3D True
.SaveData =3D True
.AdjustColumnWidth =3D True
.RefreshPeriod =3D 0
.PreserveColumnInfo =3D True
.Refresh BackgroundQuery:=3DFalse
End With
End Sub Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981388
VBA and Pivot Table Query for Text values
I think I am having a problem with my code because if the value in a cell is
text instead of numeric, the symbol ' needs to be added to the code. In the
code below, where would I put the ' to switch the variable deptNUM from
numeric to text?
& "WHERE (AC_CLAIM.GL_BUS_UNIT_A_NBR=" & deptNUM & ")")
Thanks Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981386
Conditional Formating
Hi Every one,
I have a sheet which currently uses Excel 2000 conditional formating
to change the cell color based on a formula that comes from another
sheet. So depending on the alphabet result that comes from the formula
the cell's color changes
for upto 3 results
L-Yellow
M-Orange
H-Red
But now we have a new requirement which says a new
N-Green is to be added.
I have tried the macro Worksheet_Change suggested by others for the
worksheet but it only works when there an direct addition to cell and
some key pressed. Can any one suggest another alternative, I am new to
VBA so any help is appreciated. Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981381
Converting Excel Equation into VBA
I currently have a spreadsheet that starts up with a series of userforms.
Within these userforms an employee will select certain criteria to base
equations on. I've built all of the dialog boxes and everything works great.
The issue I'm having now is that when the employee hits the final command
button I want an equation to populate. The current example is very simple
and only has one requirement. I originally wrote the equation in excel to
first demonstrate that using several SUMIFS we could accomplish what we
needed. I was then told that there could be many different criteria and I
would prefer to have excel only calculate one equation instead of many if
they only needed one at a time. Below is the current excel equation.
=((SUMIFS('recovery idea.xlsm'!PAID2003,'recovery
idea.xlsm'!DAYS2003,">="&$A2,'recovery idea.xlsm'!DAYS2003,"<="&$B2,'recovery
idea.xlsm'!DTEOFPAY2003,"<="&'recovery idea.xlsm'!MAXDATE))+(SUMIFS('recovery
idea.xlsm'!PAID2007,'recovery idea.xlsm'!DAYS2007,">"&$A2,'recovery
idea.xlsm'!DAYS2007,"<="&$B2,'recovery
idea.xlsm'!DTEOFPAY2007,"<="&MAXDATE)))/(SUMIF(REFERREDDTE,"<"&MAXDATE-RecoveryRates!$A2,BALREFERRED))
A2 currently equals 0 and B2 equals 30. A3 would equal 31 and B3 60... so
on and so forth. The first two SUMIFS gather amounts that meet the criteria
(in this case any payment made prior to the MAXDATE, the max date is being
pulled from the last dialog box). The third SUMIF divides the total by the
amount that was referred. Basically showing the amount collected over the
total possible.
Below is the current code for when the command button is hit.
Private Sub CommandButton2_Click()
Sheet4.Cells(2, "H") = TextBox1.Value
Unload AllLegalNone
End Sub
Thanks! Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981380
Is there a test for an empty string that returns a boolean?
Hi
I'm passing the ElevCptn parameter as Optional:
Private Sub SetOptBut(FontBack As Boolean, PlnCptn As String, Optional
ElevCptn As String)
I want to test to see if the parameter has been included & Enable a
button if it is, & Disable it if is isn't, so I'd like it to return a
boolean.
Something like this:
CboElevation.Enabled = IsEmpty(ElevCptn)
Except that doesn't work because "" isn't an empty string.
Any help is appreciated
Ta
Dave F. Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981375
Macro - How do i....?
I have a worksheet that starts out like this with 5 columns:
A B C D
E
1000 0 14630.17 Bank of Amer Disbursement Account
2200 0 250.64 401K Pension Due
6220 5192.31 0 Staff Veterinary Salaries 0
6223 1179.74 0 Veterinary Assistant Wages 157.81
6224 908.5 0 Receptionist Wages 79
6225 4843.19 0 Veterinary Technician Wages 410.98
6227 1346.15 0 Practice Manager Wages 0
6245 262 0 Grooming Payroll Expenses 0
6290 1380 0 Payroll Taxes
6290 0 3525.19 Payroll Taxes-ee
6290 3525.19 0 Payroll Taxes-ee
6315 0 14.4 Disability Insurance
6330 0 342 Medical/Dental Insurance
6355 125.32 0 Retirement Plan Contributions
The first part of the marco I have created creates a new column before A and
takes the amount in row 1column C and pastes it to all rows that hold
information then deletes first row.
Looks like this:
A B C D E
F
14630.17 2200 0 250.64 401K Pension Due
14630.17 6220 5192.31 0 Staff Veterinary Salaries 0
14630.17 6223 1179.74 0 Veterinary Asst Wage 157.81
14630.17 6224 908.5 0 Receptionist Wages 79
14630.17 6225 4843.19 0 Veterinary TechWages 410.98
14630.17 6227 1346.15 0 Practice Manager Wages 0
14630.17 6245 262 0 Grooming Payroll Expenses 0
14630.17 6290 1380 0 Payroll Taxes
14630.17 6290 0 3525.19 Payroll Taxes-ee
14630.17 6290 3525.19 0 Payroll Taxes-ee
14630.17 6315 0 14.4 Disability Insurance
14630.17 6330 0 342 Medical/Dental Insurance
14630.17 6355 125.32 0 Retirement Plan Contributions
The next part I am unsure of. What I need to do is take the amounts in
column F and move them to the bottom of column C. I also need to eliminate
spaces. The catch is that this document may never be exactly the same so it
cannot be cell specific.
Any suggestions are greatly appreciated. This is my first time working with
macros. If you need more info please let me know.
Thanks,
--
Malissa Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981370
=sum doesn't work properly
I'm confused! I've been entering =sum formulas without a problem. Suddenly,
today, all I get is a "0" total instead of the sum of the column. For
instance, =sum(d36:d43) should equal 17, but it shows a 0. The cells are
formatted as "general". What happened? Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981366
Function Call to check Workbooks.Open
HI there... I written a Sub that opens Excel files from a network
drive and imports them into an Excel sheet one at a time in an append
fashion inside a loop. So, I have create a different Sub previously
that export Excel files to their respective network folders, and then
employees work on it, and then we needed an automatic way to import
them all in for analysis. I have a Log from from the previous export
process so records the locations/file names of all the excel files
that were exported, so I use that to import them back in.
My Sub works very well, but I wanted to include a way to check when
the File is being opened for different supprise conditions that can
arise, including errors.
I am not sure how Excel behaves in opening a file if someone else has
it open and if working on it.
or
IF the file is missing for some reason from the expected location
where it should be
or whatever other conditions there exists that could become
problematic, basically I want the script to cycle through all the
files and handle errors or problems gracefully so that the VBA don't
error out.
Anyways, I am posting my entire VBA script that I did, which works,
but I wanted to add some handling, and I am not sure if a Function
Call would be appropriate here, have little experience with Function
Calls. Also, I consider my skills like beginner to maybe a little
intermediate, so if anyone has suggestions on making the script
better, let me know.
But basically it reads the locations and file names from a Log sheet,
so addresses would be something like this:
Location the Store folders were written to:
c:\Documents and Settings\LANID\Desktop\test_store_import\Store_1_.xls
c:\Documents and Settings\LANID\Desktop\test_store_import\Store_2_.xls
Location the Store folders were written to, but in the real script
they will point to network drives, instead of my local computer for
testing..... The script also strips the store number value from the
file name, so that the right sheet for copy can be selected, which in
this example would be a sheet named 1, which represents store number
1, thanks a lot!
code below ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++
Sub Import_From_Log()
Dim wsLogName, wsOpenName As String
Dim wsNew, wsLog As Worksheet ' New sheet that will contain all the
imported Excel sheets, appended in one sheet
Dim wsLogRange As Range
Dim FileToOpen, get_store_number As String
Dim lastrow, lastrow2, lastrow3, real As Long
Dim RangeCell As Range
Dim Switch As Worksheet
wsLogName = ActiveWorkbook.Name
Set wsLog = ActiveSheet
Application.ScreenUpdating = False
With wsLog
Sheets("Log").Activate
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row ' last row is
stored in variable RangeFinder
Set wsLogRange = Range("A2:A" & lastrow)
End With
Set wsNew = Sheets.Add ' Add the new workbook that will contain the
updated lists
wsNew.Name = "File_Import_Appended"
For Each Value2 In wsLogRange
FileToOpen = Value2
With wsLog
Workbooks.Open FileName:=FileToOpen ' works up to here
wsOpenName = ActiveWorkbook.Name ' record the name of Excel
file just opened into a variable for later usage...
get_store_number = Left(Mid(wsOpenName, 7), 1) ' gets the value
for the sheet number to select and copy from, ignore all else
ActiveWorkbook.Sheets(get_store_number).Activate ' make sure to
only select the store sheets to copy from
Range("A1").Select ' start by placing cursor into cell A1
Selection.End(xlToRight).Select ' find last column
Selection.End(xlDown).Select ' next - find last row
Set RangeCell = ActiveCell ' record the right
side of the range and assign to RangeCell
Range("A1:" & RangeCell.Address()).Select ' select
the range
Selection.Copy ' now
copy it
Windows(wsLogName).Activate ' switch
back to the Append sheet
ActiveWorkbook.Sheets("File_Import_Appended").Activate ' make
sure we are activating the append sheet
With wsNew
lastrow2 = .Range("A" & .Rows.Count).End(xlUp).Row
End With
If lastrow2 = 1 Then ' if sheet is blank that mean nothing was
pasted, so start here for the first paste
ActiveSheet.Paste
Else
real = lastrow2 + 1 ' if the sheet was already posted, we want
all other pastes to follow this rule here
Range("A" & real).Select
ActiveSheet.Paste
End If
Application.Windows(wsOpenName).Activate
Application.CutCopyMode = False ' clear the clipboard, otherwise a
window will popup asking if clipboard contents should be saved
ActiveWorkbook.Close SaveChanges:=False ' close the Import
Excel Workbook
End With
Next Value2
With wsNew
Cells.EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
End Sub Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981362
Calculating Cells
What causes Calculating Cell 100% and locking up of the entire MS-Excel
program? Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981360
Open to today's date
Hello
I have a workbook with a sheet for each week of the year. The dates are in
the same row in each sheet. I would like to be able to have the workbook
open to the sheet that contains today's date.
Thanks for your help! Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981355
need help tweaking Debra Dalgleish macro
greetings, i pulled the following code from ms. dalgleish's data validation
file and would like the macro to stop once it reaches at certain row, if the
last number is continually overwritten that's fine.
i've tried if statements based on "target.row + 1" being less than a
numerical value, say 10, but that doesn't seem to work (if someone could clue
me into why that'd be great. any help appreciated, and props to ms.
dalgleish and her fab, i.e. incredibly useful, website =D
http://www.contextures.com/index.html
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
lCol = Target.Column 'column with data validation cell
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Select Case Target.Column
Case 2, 3, 4, 5, 6
If Target.Offset(1, 0).Value = "" Then
lRow = Target.Row + 1
Else
lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1
End If
Cells(lRow, lCol).Value = Target.Value
End Select
End If
exitHandler:
Application.EnableEvents = True
End Sub Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981353
Checkboxes and Arrays
When working with Check Boxes, is it not possible to use arrays to keep track
of them, as with worksheets?
SET UP
Dim all_boxes As Variant
' setting up ranges
Set range_create = Worksheets("INTRO").Range("F7")
What I am trying to do:
' create array listing the names of every possible sheet (there are MANY
more sheets than listed here)
all_boxes = Array("create_111", "create_112", "create_113")
' select all other check boxes by making them true
For offset_var = 0 To 2 (there are normally 27 check boxes)
Worksheets("INTRO").Array(all_boxes(offset_var)).Value = True
Next offset_var
What works:
Worksheets("INTRO").create_111 = True
Worksheets("INTRO").create_112 = True
Worksheets("INTRO").create_113 = True
' etc... this takes up a LOT of space, and I always like trying to make more
efficient code, if possible
Problem:
"Object doesn't support this property or method"
Attempts to fix it:
- I have tried putting the names in the array without "" (quotation marks)
- I have tried removing the Array
Worksheets("INTRO").(all_boxes(offset_var)).Value
- I have tried without the Value
Worksheets("INTRO").(all_boxes(offset_var))
- I have tried using Shapes with and without Value
Worksheets("INTRO").Shapes(all_boxes(offset_var))
Worksheets("INTRO").Shapes(all_boxes(offset_var)).Value
It is problem something extremely simple...
Thanks for any help! Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981346
Formula for selecting highlighted cells only
Is there a formula I can use to capture only highlighted cells in a row? Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981344
currency formatting of cells with code
Hi, below is the code I have so users from different countries can enter
their currency symbol. e.g $ or £ or ? or "Ksh" (Kenya Shillings), Bht
(Thai Baht), etc.
By them entering their currency symbol (or letter code) into cell M2
(Cells(2, 13)), the Change event fires and it formats a Range
(OtherCurrencyCells), with this format.
It works quite well, but when I try to enter certain letters into M2,
nothing happens. For about 1/3 of letters it will fire, but won't change the
format. E.g. It accepts all the currency symbols I've tried so far, but
when I try to enter "ksh", or "bht", it won't change. But it will change for
"kqr" and some other single and multiletter combinations. I have no idea the
reason or the pattern. It will change for "k", but if I enter "ksh", it only
takes the "k" as the currency format. But if I put any of these
unacceptable ones inside "quotes", it takes them all.
So I guess that could be my solution, to have users enter letter codes for
their currency format, e.g. Ksh, Bht, in double quotes.
But now I'm just plain curious as to why it won't accept certain of these
letters. Any ideas? Thanks, Harold
Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
On Error GoTo ErrHandler:
If Intersect(t, Range("A1:z115")) Is Nothing Then Exit Sub
Range("OtherCurrencyCells").NumberFormat = Cells(2, 13).Value & "* #,##0"
Exit Sub
ErrHandler:
End Sub Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981343
Record Macro Window.
Hi,
I cannot see the small window which pop's up when we select record
macro from the option tools>Macro>
Can any one help tell me to to get it back so that i can record the
macro with or without refrence mode.
Regards
Heera Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981339
worksheet menu bar
Running 2003, xp pro sp 2.
User cannot access the Options item on the Tools submenu in the Worksheet
Menu Bar.
At least one spreadsheet is open.
Options is not greyed out.
When anyone else logs onto her machine, it works perfectly.
I logged on as Administrator and ran Repair via add/change programs - didn't
help her.
The registry contains the following in
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common\Toolbars\Settings
Value 7
Name: Microsoft Excel AWDropdownHidden
Type: REG_DWORD
Data: 0x1
Anyone know what this is?
Should I remove it?
She swears she has not deleted or changed
anything and that it worked fine yesterday.
Any ideas about what may have happened?
Thanks - Mike Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981337
Any sample to connect a remoted database through Excel?
Does anyone have any sample to connect a remoted database through Excel?
I need to make a connection with a database in China, and I locate in HK.
Could anyone give me any suggestions on what I need to retrieve any data from
a database?
What I can think of is
URL, username, password, ...
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981323
VBA formula too long?
Hi,
I am creating a utilisation tool in Excel which contains data on Sickness,
Holidays and Other Absence. I have created the formula below in Excel to
account for how much Other Absence has been taken year to date.
I have then transferred this into VBA and changed the format to R1C1 and
adapted the ranges so that they are defined by two variables (j - start of
year, based on what today's date is & k - end of year, based on what today's
date is). See code below. In reformatting however the formula is pushed onto
a new line of code and the usual "_" does not seem to read as me wanting the
code to continue on the next line.
Is it possible to have a formula that is simply too long?
I am getting more confident in Excel and VBA now and can pretty much do
everything I want to, but I am equally confident that there is a better way
of doing everything that I do :) I am not especially fond of the formula /
code below so please feel free to scratch it if that provides a more simple
solution.
(I haven't finished reformatting after I encountered the problem.)
EXCEL FORMULA
=(SUMPRODUCT((A26:A391<B1)*(I26:I391<>"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-((SUMPRODUCT((A26:A391<B1)*(I26:I391<>"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Education")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Child")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Doctor")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Dentist")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Emergency")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Maternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Paternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Flexi"))))
REFORMATTED VBA CODE
ActiveCell.Offset(-11, 0).FormulaR1C1 = "=(SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C<>"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" &
j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-((SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C<>"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" &
j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-(SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C=""Education"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 &
"]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C=""Child"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1_
<B1)*(I26:I391=""Doctor"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Dentist"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Emergency"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Maternity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Paternity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Flexi""))))"
Thanks in advance,
JohnP Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981321
Getting folder size information into cell???
Hello,
I can't seem to figure out how to get folder size information into an excel
spreadsheet cell. I have the filer/server location information in one cell
and I'd like to use the information within that cell to extract the
folder/directory size and populate another cell. I can't seem to figure out
how to go about this? Any suggestions and/or solutions (if any) would be
greatly appreciated.
Please bear in mind, I'm a noob with this excel programming. ;-)
Thank you. Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981320
Merge muliple sheets in multiple workbook below each other.
how do i Merge muliple sheets in multiple workbook (in same folder) below
each other. However each respective sheet should go to a new sheet. Note all
sheets in individual workbook are named same.
Please help me. all responses appreciated.
Thanks Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981319
workbook / worksheet change event
Is it possible to use the act of navigating to a different worksheet as an
event in VBA programming? For instance, if I had a workbook with multiple
worksheets, and the primary sheet that is displayed when the workbook is
opened is called "setup", I want to make sure that you cannot navigate to or
view any other worksheets until all necessary changes are made to the setup
worksheet. Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981298
Pulling data from 3000 files
Hi All,
I'm going crazy try to copy and paste data from 3000 files into 1 file. Is
there a way to automatically open a file, copy columns 1 -5 and then append
it to the file where I want it all placed?
Thanks in advance... Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981297
My code enters break mode but there are no breakpoints
For some odd reason my code is acting like there are breakpoints in a handful
of locations. I tried exporting the module, deleting the old excel file,
creating a new excel file, importing the module, and running it. Same thing
happened. Then I copied the code into Word, deleted the Excel file,
re-created it, and pasted the code into a new module. Same problem.
Any ideas???
I'm using Excel 2007. Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981287
Macro to show/hide rows
I need to use a single cell to show/hide other rows. For instance if you put
a "6" in cell B1 it would show the next 12 rows and hide the rest. If you
put in a "10" it would show the next 20 rows and hide the rest, etc.
I'm new to writing macros, sorry! Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981276
Variable Input
I have to use, what I thought was, a simple variable input, but it's turning
out to be not so simple. Basically I loop through all sheets in a book, as
long as the sheets <> "Sheet1". Then do some basic formatting and some
simple math. Everything works fine except for this part:
Chng = Range("F2")
and this part:
c.Offset(, 19).Value =
"=(IF(RC[-20]="&Chng&",RC[-18],0)+IF(RC[-15]="&Chng&",RC[-14],0))*RC[-3]"
I have names in column F of each sheet, which are all the same on each
sheet, but different names are on different sheets. All I need to do is
figure out the value in F2 on each sheet and use that in my IF function. I
thought I could just assign the value in F2 to the variable Chng and then
just use that variable in my IF function. Excel has other thoughts and it is
telling me that I can't do that. Why?
All code below:
Sub Math()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet
Dim myA As Range
Dim lastYRow As Long
Dim sumRng As Range
Dim Tot As Double
Dim Chng As Variant
For Each sh In Worksheets
If LCase(sh.Name) <> LCase("Sheet1") Then
sh.Activate
With sh
.Rows(1).Font.Bold = True
Chng = Range("F2")
lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For Each c In .Range("F2:F" & lastRow)
If c.Value <> "" Then
c.Offset(, 19).Value =
"=(IF(RC[-20]="&Chng&",RC[-18],0)+IF(RC[-15]="&Chng&",RC[-14],0))*RC[-3]"
End If
Next c
lastYRow = .Cells(.Rows.Count, "Y").End(xlUp).Row
Set sumRng = .Range("Y2:Y" & lastYRow)
Tot = Application.WorksheetFunction.Sum(sumRng)
'MsgBox Tot
.Cells(lastRow + 1, "Y").Value = Tot
'.Rows(2).Delete
End With
End If
Next sh
End Sub
Any help is appreciated.
Regards,
Ryan--
--
RyGuy Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981275
Macro Error Help!!
Please I need help in resolving this error "Sub or function not defined". I
tried using Ron's code http://www.rondebruin.nl/copy3.htm and i get error.
Please help me in identifying what am doing wrong. all responses appreciated.
Sub RDB_Module1() ***** This get highlighted
Dim myFiles As Variant
Dim myCountOfFiles As Long
myCountOfFiles = Get_File_Names( _ ****this also gets highlighted
I believe these two function are not properly called or referenced to. HELP
Thanks
Yossy Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981273
Count function?
If Col A contains a text variable and column B contains a numerical variable
associated with the text variable, what is the simplest formula I can put
into a single cell that will count the number of occurrences of the numerical
variables in Col B associated with the text variable in A? Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981272
Appending Contents Of Multiple Spreadsheets Into One
Hello. I would like to open up a new spreadsheet and append rows 2 -
EOF from six other spreadsheets into that new one, be prompted for the
name to save it with, then save and quit. Would someone be kind
enough to offer some VBA code to do this with?
Thank you,
Sheldon Potolsky Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981259
What's the API call to kill the KB buffer?
I don't want keystrokes typed in advance of my UserForm appearing to
remain alive and execute.
Anyone know the API call (which I assume this requires) to flush the
keyboard buffer?
Thanks much.
*** Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981247
ListBox Displaying With Some Items Already Selected In 2007
I have an application that I have been using without problems since Office
2000. It has a list box, which is populated using the command:
cyo_dialog.sel_box.RowSource = "product_list"
It then goes on to ensure that all the items are deselected with the code:
For a = 0 To (cyo_dialog.sel_box.ListCount - 1)
cyo_dialog.sel_box.Selected(a) = False
Next a
cyo_dialog.sel_box.ListIndex = 0
Since I started using this on 2007, for some unknown reason two of the first
three items come up selected (normally item 1 with either 2 or 3) , and are
then not deselected by the code that follows.
I have found 2007 to be very buggy and inconsistent in its running, but I
can see no reason why this should now be happening.
Thanks in advance. Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981246
Checking if a cell contains a string that is not case sensitive
I am trying to count how many times a specific string "abc" forms part of a
cell in a spreadsheet.
For example
abcgfd, abcdse, efgabc would be vaild and I would like to count these
abdcef, bcadef, cbaeds would not be valid and I would not want to count these.
Can anyone help point me in the right direction?
Thanks. Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981244
TreeView's pos on UserForm changes by itself.
Until I complete the excruciating process of finding out how to
disable screen updating while pruning a TreeView (deleting & moving
nodes), I hit on the idea of setting TreeView.Visible = False, then =
True again when processing's done.
When I do, the TV's re-shows in a DIFFERENT position on the UserForm
than before. (It has shifted to what seems like the U.L.-most corner.)
NOW what's going on?
Thanks.
*** Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981243
updating pivot table data via external xls file which is replaced
hi,
the problem with my code for updating the table is that it works on my
computer but if i try it on another one the pivot table itself isn't updated
but a new worksheet created with an empty pivot in it
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"U:\xyz\xyz_Shared\test\TestLines.xls!R4C1:R50000C38"
The file from which the table should get it's data is called TestLines.xls Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981240
Numeric data
Dear all
Pleasae guide me to find out numeric data in cell where the cell
contains both aplha & numeric value.
Like i want to take out pincode from one single cell where in the
location and pincode is mentioned in a single cell
Eg ( Mumbai 400018 )
Serch Result shud b 400018
Thanxs in advance Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981235
Cell references and merged cells
Is there an explanation somewhere of how to handle column references
when there are merged cells in another row?
For example:
Private Sub TestOffset()
'Expect references to columns A, B, and C.
'Get references to columns AB(merged), C, and D, if there are merged
cells in other rows
Dim r As Long
Dim rng As Excel.Range
Range("A1").Activate
Set rng = Range("A1")
With rng
Debug.Print .Address
.Value = 1
.Offset(0, 1) = 2
.Offset(0, 2) = 3
For r = 0 To 12 Step 6
Debug.Print .Offset(r, 0).Address, .Offset(r, 2).Address,
.Offset(r, 4).Address, .Offset(r, 6).Address
Next r
End With
Range("A20").Activate
Set rng = Range("A20")
Range("A20:B20").Merge
With rng
Debug.Print .Address
.Value = 11
.Offset(0, 1) = 12
.Offset(0, 2) = 13
For r = 0 To 12 Step 6
Debug.Print .Offset(r, 0).Address, .Offset(r, 2).Address,
.Offset(r, 4).Address, .Offset(r, 6).Address
Next r
End With
End Sub Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981232
Excel 2007 SP1 VBA Help
I have created a WortArt object in Excel programatically using VBA and using
the preset text effect style msoTextEffect3. Unfortunately, this creates text
with the color red. Now I can right click on the word art and change the
color of the text to whatever I want. But I cannot find a way to do this
programmatically in VBA. Also, trying to use Macro recording is useless for
this. It looks like macro recording is broken in Excel 2007.
Any help grateful
Wheeley Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981230
Help to speed up this macro
I have the following macro that runsa a UDF to delete blank rows (every other
row). The formula is inserted in cell E11 and copied down for for each line
where Col B is not empty. It works just fine, however, there are only 100+
lines and it runs very slowly. Is there something Ican do to speed it up?
Sub BudgetJobNo()
'
Sheets("Budget").Select
Call RowsJoinData(2) '<where column 2 (B) is blank for every row to delete
Application.DisplayAlerts = False
Application.ScreenUpdating = False
JobNo = "=IF(ISERROR(LEFT(Budget!B11,FIND(""
"",Budget!B11)-1))=FALSE,LEFT(Budget!B11,FIND("" "",Budget!B11)-1),"" "")"
Range("E11").Value = JobNo
'And then copy them down to the last line.
Range("e11:e11").Select
Selection.Copy
CR = 11
Do While Cells(CR, 2) <> ""
Range(Cells(CR, 5), Cells(CR, 5)).Select
ActiveSheet.Paste
CR = CR + 1
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
--
Jim Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981229
How do you collapse a TreeView?
I cannot seem to find even basic properties in VBA's TreeView.
There is Node.Collapse, which collapses the immediate children of a
single node. Gee, Beaver, that's swell.
How do you collapse the tree? At the risk of being obvious, I mean:
every node.
Thanks much.
*** Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981228
WM_SETREDRAW doesn't work unless...what?
RM Smissaert kindly replied to my recent Q of how to disable screen
updating in a TreeView:
Private Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal HWND As Long, ByVal wMsg _
As Long, ByVal wParam As Long, lParam As Any) As Long
Private Const WM_SETREDRAW = &HB
***
SendMessage TreeView1.HWND, WM_SETREDRAW, IIf(EnableTheBugger, 1, 0),
0
(The 1 and 0 in my IIf are reversed from his example, which I presume
is correct, 'cause it seems to work for me.)
But this call fails, UNLESS (I found empirically) I, like, make the
call, then show a MsgBox (which interrupts processing and "seeds" the
screen first...?) Sort of like having to turn off the pilot before
turning off your stove.
What additional API call must I make if any to make WM_SETREDRAW work?
***
Better yet, how do you do this in a native VBA way?
What I'm doing is cleaning & pruning the tree--deleting & moving
hundreds or even thousands of nodes. Each operation of which has to
wait for my ancient video card, which I inherited from Johannes
Vermeer, to repaint (in slow-to-dry Old Master oils) my screen.
Thanks much.
*** Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981227
Confrimation of copy paste operation
Hi,
In my application (C#, Office 2007), i am attempting to copy a range of
cells containing data to a range on another worksheet. Upon copying the range
I then copy a chart into a specific area on the second worksheet. The chart
is resized and set up to represent a datapoint per row of data so the chart.
Occasionally (and i mean occasionally, i cannot replicate the problem 100%
of the time) the chart does not get copied to the second worksheet. I do this
a number of times for various different table ranges. Sometimes there will be
a number of these charts copied to the same output worksheet and i have
encountered this issue where by only some of the charts copy and others don't.
I generally use the following code to do the copy paste
_chObj = (ChartObject)_excelDataWS.ChartObjects("ChartName");
_chObj.Copy();
_excelPresentationWS.Paste(_presTopLeftRange), paramMissing);
I have a couple of questions on this problem.
1) Is there a benefit to using the following copy command?:
_chObj.CopyPicture(XlPictureAppearance.xlScreen,
XlCopyPictureFormat.xlPicture);
2) Is there any way to confirm that a copy/paste operation has succeeded and
if not then to retry it?
I'm really having no joy in diagnosing why this is happening. I get no
exceptions thrown when the charts do not appear.
Any help is greatfully recieved.
Thanks in advance,
G Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981226
Capture paste from Office Clipboard
Hi,
A bit of background first
I have an event macro which I want to prevent from running if the user
paste some info from the office clipboard into a cell as it triggers
my events.
The below code works if the data is copied from one section of the
spreadsheet to another. Is there anyway to capture Ctrl + V using
select case?.
Select Case Application.CutCopyMode
Case Is = False
MsgBox "Not in Cut or Copy mode"
Case Is = xlCopy
MsgBox "In Copy mode"
Case Is = xlCut
MsgBox "In Cut mode"
End Select
FYI
I also looked at the on key event but didn't want to add a new module
to the workbook if I can avoid it
Application.OnKey "^v", "Macro1"
If I had to add a module I guess Chip site would be the best start??
http://www.cpearson.com/excel/vbe.aspx
Any suggestions of tips would be appreciated
Regards
VBA Noob Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981221
Modeless Form help please
I'm using a modeless form with a spreadsheet. The reason for making it
modeless is that I want to be able to use the spreadsheet in the background
and refer to the form from time to time. However, When the form is displayed
it disables the wheel on the mouse (which is quite a problem for me)...
1. Is there a way around this?
2. What else is different when a modeless form is displayed?
Thanks
M Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981219
Can i link various text in a cell to a picture
I would like to link text in a cell to a picture so when i select or write
text in a cell different pictures will appear next to the cell the text is in. Tag: How to replace the image on the Office Button in Excel 2007? Tag: 981217
Can anyone point me in the right direction to figure out how to alter the
image seen on the Excel 2007 Office Button in the top left?