Over 1 million tech questions and answers.

Solved: How to get to Excel VBA code modules using VBA?

Q: Solved: How to get to Excel VBA code modules using VBA?

In MS Access you can write VBA code that allows you to manipulate the existing VBA code in standard and class modules by using the Modules collection of the application object.

In Access VBA, the Application.Modules collection contains only open modules

I am trying to do a similar same thing in Excel but with no luck.

There appears to be an undocumented Modules collection in Excel but the Modules.Count always comes back as zero. If this works the same way as in Access VBA, then none of the modules are officially "open" (including the one I'm executing).

Anyone have any experience doing this sort of thing in MS Excel VBA?

I just want to write some quick and dirty code to do some rudimentary analysis of an Excel application that was dropped on my desk. I want to do things like counting lines of code, and identifying the entry points and global declarations.

Yes, I can cut and paste all of the code into a text file and analyze that but I was looking for a more elegant solution.

RELEVANCY SCORE 200
Preferred Solution: Solved: How to get to Excel VBA code modules using VBA?

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: How to get to Excel VBA code modules using VBA?

Read other 7 answers
RELEVANCY SCORE 67.6

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows XP Home Edition, Service Pack 3, v.3264, 32 bit. XP = Version 2002
Processor: Intel Pentium III Xeon processor, x86 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 1981 Mb
Graphics Card: Intel(R) G41 Express Chipset, 256 Mb
Hard Drives: C: Total - 39989 MB, Free - 6566 MB; D: Total - 36294 MB, Free - 8670 MB;
Motherboard: ECS, G41T-M
Antivirus: PC Cleaner Pro, Updated: Yes, On-Demand Scanner: Disabled

Hi
I use the VBA code for a program I wrote quite often and keep adding to it to improve, since last year I find on opening the code window that the modules for the vbacode in a smaller size, then I have to move it and drag the size to fit the window. It always fitted the space on the righthand side. Did I do something wrong for it to happen, and can it be fixed?

Find attached file just to show what it looks like

Please help

Thanks
Rusty
 

A:Solved: vba code modules sizes

Read other 10 answers
RELEVANCY SCORE 66.4

Hi All,

Having some trouble exporting modules from code. Not sure why. Any help would be appreciated. I am getting Run-time error '50012' Method 'Export' of object '_VBComponent' failed. I am using an .xls workbook in Excel 2007.

My code is below, but the export breaks even when I do this:
Code:
strPath = "C:\ReplaceTaxCode"
ActiveWorkbook.VBProject.VBComponents("ToolsA").Export "\" & strPath
Full Code Here:

Code:
Public Sub ReplaceCode()

Dim wkbCode, wkbReplace As Workbook
Dim strPath
Dim VBAEditor As VBIDE.VBE
Dim objProject As VBIDE.VBProject
Dim objComponent As VBIDE.VBComponent

Set wkbCode = ThisWorkbook
Set VBAEditor = Application.VBE
Set objProject = VBAEditor.ActiveVBProject

strPath = "C:\ReplaceTaxCode"

For Each objComponent In objProject.VBComponents
objProject.VBComponents(objComponent.Name).Export "\" & strPath
Next

End Sub

 

A:Solved: Excel VBA Exporting Modules

Read other 7 answers
RELEVANCY SCORE 57.6

Firstly I am a beginner of beginners but I try to learn as much as I can.
I have put the following protection code into an Excel 2003 workbook and everything runs ok. I then opened the workbook in Excel 2007, and saved it as a .xlsm file. When I try to open the .xlsm file in Excel 2007, I get a vba Runtime error 13, "Type mismatch", and the code sticks at the first line below("If Worksheets...). I have tried everything that my limited knowledge allows, searched the web for 2 days, and still have no answers.
Any help would be greatly appreciated.

Private Sub Workbook_Open()
If Worksheets(28).[A51] > "" And Worksheets(28).[A50] = Worksheets(28).[A51] = False Then
MsgBox "Sorry, this program is not registered for this computer, please e-mail .......... for more info."
Application.DisplayAlerts = False
Application.Quit
End If
End Sub
My system specs are shown below.

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM) i3 CPU M 380 @ 2.53GHz, Intel64 Family 6 Model 37 Stepping 5
Processor Count: 4
RAM: 2934 Mb
Graphics Card: Intel(R) HD Graphics, 1243 Mb
Hard Drives: C: Total - 290143 MB, Free - 172186 MB;
Motherboard: Dell Inc., 0WXY9J
Antivirus: avast! Antivirus, Updated and Enabled
 

A:Solved: Excel 2003 vba code not working in Excel 2007

Read other 16 answers
RELEVANCY SCORE 56

How can I combine the following 2 bas files into one? I have a spreadsheet that in column G list cities the spread sheet list over one hundred cities but I only need two cities and the rows for those cities. This is what I have and I would like to combine it into 1 file. It searches the spreadsheet finds the city name in G and copies the row to another worksheet and names the work sheet after the city.
Can any one help this scripting Noob. Thanks
File 1
________________
Attribute VB_Name = "FTW"
Sub Extract_Data()
'this macro assumes that your first row of data is a header row.
'will copy a row from one worksheet, to another blank workbook
'IF there is FORT WASHINGTON in column G
'Variables used by the macro
Application.ScreenUpdating = False
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String
Set a = ActiveSheet
'Select Range
'(note you can change this to meet your requirements)
Range("A1:AS4000").Select
'Apply Autofilter
Selection.AutoFilter
FilterCriteria = "FORT WASHINGTON"
'NOTE - this filter is on column G (field:=7), to change
'to a different column you need to change the field number
Selection.AutoFilter field:=7, Criteria1:=FilterCriteria
'Select the visible cells (the filtered data)
Selection.SpecialCells(xlCellTypeVisible).Select
'Copy the cells
Selection.Copy
'Open a new file
'Workbooks.Add Template:="Workbook"
'Get this file's ... Read more

A:How do I combine 2 modules in excel macro?

This is not the best Forum to post an Excel thread on as most Excel gurus are on the Business Applications forum.
I mostly program Access but I will at least try and answer your question.
First of all you do not need 2 modules as they basically do exactly the same thing, so the only change appears to be the City name. So you could just add the second module to the first one, deleting the "Dim" statements from the second part and the Filter reset from the first part. The code should then run straight through both sets of code. i.e.

Attribute VB_Name = "FTW"
Sub Extract_Data()
'this macro assumes that your first row of data is a header row.
'will copy a row from one worksheet, to another blank workbook
'IF there is FORT WASHINGTON in column G
'Variables used by the macro
Application.ScreenUpdating = False
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String
Set a = ActiveSheet
'Select Range
'(note you can change this to meet your requirements)
Range("A1:AS4000").Select
'Apply Autofilter
Selection.AutoFilter
FilterCriteria = "FORT WASHINGTON"
'NOTE - this filter is on column G (field:=7), to change
'to a different column you need to change the field number
Selection.AutoFilter field:=7, Criteria1:=FilterCriteria
'Select the visible cells (the filtered data)
Selection.SpecialCells(xlCellTypeVisible).Select
'Copy the cells
Selection.Copy
'Open a new file
'... Read more

Read other 2 answers
RELEVANCY SCORE 56

Having problem with installing Cumlative Security Update for Internet Explore 7 in Window Vista Home Premium.
Message is "Windows Modules installer stopped working and was closed. A Ploblem cased the application to stop working correctly. Windows will notify you if a solution is available".
Error code is 800F0902. I went microsoft web site and elsewhere, tried any solusion I found in forum, such as deleting everything in C:\Windows\SoftwareDistribution. But didn't work.
Anybody knows how to solve this?
 

Read other answers
RELEVANCY SCORE 55.2

Long story short, I have the same excel macro book replicated a few times. Is there a really easy way so that if we make a change to code in one of the work books we can replicated the changes in the copies?
 

A:Easy way to Link modules between excel files?

http://www.cpearson.com/excel/vbe.aspx

?
 

Read other 1 answers
RELEVANCY SCORE 55.2

Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 10 Pro, 64 bit
Processor: Intel(R) Core(TM) i3-4130 CPU @ 3.40GHz, Intel64 Family 6 Model 60 Stepping 3
Processor Count: 4
RAM: 4007 Mb
Graphics Card: Intel(R) HD Graphics 4400, 1024 Mb
Hard Drives: C: 453 GB (311 GB Free); D: 10 GB (1 GB Free); E: 0 GB (0 GB Free); G: 465 GB (454 GB Free); H: 931 GB (55 GB Free);
Motherboard: Hewlett-Packard, 18E7
Antivirus: Avast Antivirus, Enabled and Updated
 

Read other answers
RELEVANCY SCORE 53.2

I can't seem to get Message Analyzer to capture network data. After clicking on "Start Local Trace"  I just get a bunch of "Windows_Kernel_Trace". And I get an error at the top that says "Modules Loaded: Errors or warnings
were found when loading modules". I'm not sure what the cause is, and what I need to do to fix this.  And this is what I get when I click on the show log button (I couldn't paste everything because it's too long, and this website won't take
it all)

6/1/2016 2:54:36 PM Information Cannot generate enum for map TCPIP_SLOW_PATH_REASON_ValueMap because it have different fields with same name
6/1/2016 2:54:36 PM Information Cannot generate enum for map TCPIP_SLOW_PATH_REASON_ValueMap because it have different fields with same name
6/1/2016 2:54:38 PM Progress Loaded cached module 'Windows_Kernel_Trace_ProcessTracking_2cc3d1b85397939c45061ef9388fb58e_4_0_7948_0.mdb'
6/1/2016 2:54:38 PM Information Module cache file doesn't exist: C:\Users\N5227\AppData\Local\Microsoft\MessageAnalyzer\CompilationCache\ExtendTCPIP_66747ecf79d16b47eac835c67c1f284b_4_0_7948_0.mdb
6/1/2016 2:54:38 PM Information Module cache file doesn't exist: C:\Users\N5227\AppData\Local\Microsoft\MessageAnalyzer\CompilationCache\ExtendTCPIP_66747ecf79d16b47eac835c67c1f284b_4_0_7948_0.mdb
6/1/2016 2:54:39 PM Error C:\Users\N5227\AppData\Local\Microsoft\MessageAnalyzer\OPNAndConfiguration\OpnForEtw\OpnForEtwProcess\TCPIPComponentExt.opn(173... Read more

Read other answers
RELEVANCY SCORE 52.4

Hi I am getting weird exception and I dont understand why, I been struggling and no luck

When I load (download) an excel file with macro from a server to my desktop and open it
I get the below error message:

Run-time error '1004':
Unable to get the CheckBoxes property of the Worksheet clas

I noticed if I open and click save, close and open again the error is not there anymore.

Why is the error gone? I am not changing anything in the file, just save and open again.

Thank you for your help
 

A:Solved: Excel VBA code

It may be that the code is refering to a named object, which can only be in such state when saved rather than being opened from temp location.
 

Read other 1 answers
RELEVANCY SCORE 52.4

I have developed a small database in excel which utilizes a UserForm to collect information for new database entries. I have only limited understanding of VBA programming, so I need a little help on the code to perform the following function.

After I type in all the required fields in my UserForm, what code do I need to insert in order for the new entries to be inserted in the first empty row of the database? I know how to set the value of a cell to the data in the UserForm, but I'm not sure how to make the computer start from the top of the database and move downward until it finds an empty row, and then insert the entered data into the row.

Any help would be greatly appreciated!

Thanks
 

A:Solved: Excel VBA Code Help

Read other 9 answers
RELEVANCY SCORE 51.6

i have 6 checkboxes on a worksheet, what i would like to happen is depending on which checkboxes are checked and then a command button pressed, a particular worksheet will open i.e

checkbox1 and checkbox2 ticked = worksheet2
checkbox3, checkbox4 and checkbox5 = worksheet4
checkbox1 and checkbox3 = worksheet 5

etc
can anyone help with the code

thanks
 

A:Solved: excel checkbox code

Hi there,

Are you able to post your file? Also, please define all combinations and which action will happen. Also, I'm not sure, because of your terminology, what is going to happen. You say you want to open a worksheet, well, you can't do that. You can open a workbook, or you can activate a worksheet, but not open a worksheet. Does that make sense? Also, please make sure you tell us exactly what it is you are trying to do, the end result
 

Read other 2 answers
RELEVANCY SCORE 51.6

Could someone please explain to me what exactly this code does?
Code:

With ws.Range("A2:B" & ws.Rows.Count)
LastRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With
 

A:Solved: Explain Excel Code

Read other 7 answers
RELEVANCY SCORE 51.6

I have a workbook with at the moment 3 worksheets in it,once i have finished them,i would like to put some code in so that when the workbook is opened the vertical and horizontal scroll bars, the sheet tabs and the main bar along the top of excel, file,edit,view, insert etc are not visable or available, any idea's on what code to use please, i know i can go into tools - options and untick the scroll bars and sheet tab sections so these do not show but i can't find anywhere to unshow the top section, i would prefer this to be done automatcally when the workbook opens.

if possible could i then set up a macro on one of the worksheets(that i could password protect) that would restore these functions if i needed to alter any parts of the sheets at a later date

i do apologise for the long winded question
many thanks fo any help offered
 

A:Solved: Excel hide code

Read other 8 answers
RELEVANCY SCORE 51.6

Hi,

I'm a very new VBA user and have spent the better part of 2 day on this great website looking for a similar problem i've run into.

My problem is that i want information from "test" workbook~"results" worksheet to automatically update the "Tech Data" worksheet as well as export an update to the "Master" workbook.

FYI, two separate people input data: iron&manganese and Corrosion&scale inhibitor residuals. This data is exported to the 'results' worksheet and the same simple formula can be used to export to "tech data" worksheet. I am having a hard time getting the 'tech data' worksheet to export to the master workbook. I'm hoping someone can help me ensure the master file compiles all informaiton being exported to it. Note that the master file has several other headings as i was hoping to expand on the starter macro.

I created the tech data worksheet so data placement matched the master workbook format.
 

A:Solved: Excel-macro code help

Read other 15 answers
RELEVANCY SCORE 51.6

Hello!!! I am out of my league on this one I am making an excel worksheet for work that will be constantly updated. To help me take the data and organize it easier I need a "date added" and a "date modified" automatically entered in (I wish I could rely on everyone in my office with inputting the data correctly but I'm not very optimistic about that). I have found a way that works to get the original date added but when I try and get the date modified to work all I get is different notification errors. I attached the file in case anyone knows an easier way to do this. Essentially the date added just needs to be linked to the First and Last name column and then date modified needs to be for the rest of the columns. Any and all advice is greatly appreciated!
 

A:Solved: Excel Code Question

Read other 9 answers
RELEVANCY SCORE 51.6

Hello to All!

Does anyone have a code/macro that will find a certain text in cells (*total) in a column, then based on the text found, search another column for the totals association with it, format the color & bold that total, and calc the sum in a 3rd cell?
Column 1 would have something like, abcTotals
defTotals
ghiTotals .........etc.
Column 2 ...something like, $63.00 (blue & bold formatting when found) ........ etc.
Column 3 would hold the sum of all the*Totals

Thanks!!!
 

A:Solved: Excel 03 Macro/Code

Read other 8 answers
RELEVANCY SCORE 51.6

I have a spread sheet that is populated by an Access 2003 database. I have a series of Cells B29:L29 that are populated with numbers which are then displayed on a form. What happens is when one of the numbers is a 0 the numbers are not aligned when displayed. I'm thinking that is I could set another set of cells to search through the first set of cells to toss out the 0 and populate with whats left it would preclude the missalilgnment of the cells. I need some assistance with coding the cells to to this though.

Thanks
 

A:Solved: Excel 2003 code

Read other 9 answers
RELEVANCY SCORE 51.6

Hi all,

Have been tinkering with this rather good piece of code I found on interweb. I am really struggling with my limited VB experience and would like the amount returned to be an HLOOKUP or a VLOOKUP. Basically everything works perfectly but I would like the routine to look up say 'total' and then look at the next square and return that amount.

Any help appreciated. Code below

Sub SearchFolders()
Dim fso As Object
Dim fld As Object
Dim sfl As Object
Dim strSearch As String
Dim strPath As String
Dim strFile As String
Dim wOut As Worksheet
Dim wbk As Workbook
Dim wks As Worksheet
Dim lRow As Long
Dim rFound As Range
Dim strFirstAddress As String

On Error GoTo ErrHandler
Application.ScreenUpdating = False

'Change as desired
strPath = "c:\MyFolder"
strSearch = "Specific text"

Set wOut = Worksheets.Add
lRow = 1
With wOut
.Cells(lRow, 1) = "Workbook"
.Cells(lRow, 2) = "Worksheet"
.Cells(lRow, 3) = "Cell"
.Cells(lRow, 4) = "Text in Cell"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(strPath)

strFile = Dir(strPath & "\*.xls*")
Do While strFile <> ""
Set wbk = Workbooks.Open _
(Filename:=strPath & "\" & strFile, _
UpdateLinks:=0, _
ReadOnly:=True, _
AddToMRU:=False)

For Each wks In wbk.Worksheets
Set rFound = wks.UsedRange.Find(strSearch)
If Not rFound Is Nothing Then
strFirstAddress = rFound.Address
End If
Do
I... Read more

A:Solved: Excel 2003 VB code help

Read other 6 answers
RELEVANCY SCORE 51.6

The attached workbook has code that takes the Excel data and generates an XML file. Everything works fine except one area.

When the code is ran right now, it creates an XML File that looks like this...

<data>
<student><id>1</id>
<name>Raymond</name>
<age>11</age>
<grade>
<mark>0</mark>
</grade>
</student>

<student><id>1</id>
<name>Raymond</name>
<age>11</age>
<grade>
<mark>95</mark>
</grade>
</student>

However, I would like the code to look like this...

<data>
<student><id>1</id>
<name>Raymond</name>
<age>11</age>
<grade>
<mark>0</mark>
</grade>
<grade>
<mark>95</mark>
</grade>
</student>

Any help in this matter will be greatly appreciated.
 

A:Solved: Excel 2007 Code

Read other 16 answers
RELEVANCY SCORE 51.6

I have a macro in excel which auto fills a specified column with the formula in the first cell.

Assuming that Row 1 contains my headers, the code looks as follows:

Range("M2").Select
Selection.AutoFill Destination:=Range(M2:M1845)

Is there a way instead of specifying the range to ask the macro to just carry on filling the formula until it reaches the end of the data entered, i.e. the last row of data?

I'm sure it was something like Range = xlDown, but every attempt seems to fail.

I am running Excel 2007 by the way.
 

A:Solved: Excel Macro Code

Try using

Selection.AutoFill Destination:=Range("M2:M" & Cells(Rows.Count, "M").End(xlUp).Row)

Rollin
 

Read other 2 answers
RELEVANCY SCORE 51.2

Could someone please eplain the following line of code?
Code:

.Formula = "=HYPERLINK(""mailto:""&LOWER(A" & Target.Row & ")&"".""&LOWER(B" & Target.Row & _
")&""@nowhere.com"")',LOWER(A" & Target.Row & ")&"".""&LOWER(B" & Target.Row & ")&""@nowhere.com"")"

Why must the code use for sets of the LOWER Function?
 

A:Solved: Excel Code Line Explanation

Read other 11 answers
RELEVANCY SCORE 51.2

Could someone please help me to enter comments for the two codes below?
Code:

For I = 4 To 8
With WorksheetFunction
Iday = tsh.Cells(6, I).Value 'Row 6 Columns D thru H
If .CountIf(hd.Columns("A:A"), Iday) = 1 Then
hd.Range("A1").Value = Iday
tsh.Cells(12, I).Value = 8
hd.Range("A1").Value = ""
ElseIf WorksheetFunction.CountIf(ThisWorkbook.Worksheets("HD").Columns("A:A"), Tday) = 0 Then
x = tsh.Range("N4").Value
tsh.Cells(8, x).Select
If tsh.Cells(11, x).Value = "" Then
tsh.Cells(8, x).Value = 8
End If
End If
End With
Next

Code:

For z = 3 To 15
With WorksheetFunction
result = hd.Cells(z, 4).Value 'Column D
If entry = "Holiday" Then
result = hd.Cells(z, 3).Value ' Column C
tsh.Range("K12").Value = result
Exit Function
Else
tsh.Range("K12").Value = ""
End If
End With
Next
 

A:Solved: Excel Code Explanation/Comment

Read other 6 answers
RELEVANCY SCORE 51.2

Hi,

could some one please help me here is what i have

i have a excel spreadsheet with data and formulas in it for a quote for a mates pc business he wants me to make him a template
and he needs the code so when he runs the code it comes up with a input box asking for the users email to send it to and all of the emails with the subject "Quote"
can some one please help me?
 

A:Solved: Excel VBA code to email as attachment

Read other 7 answers
RELEVANCY SCORE 51.2

Is there a code to where I can have a macro run every 5 seconds? If so, where would I place the code?
 

A:Solved: Excel Macro/Run Code Every 5 Seconds

Hi
here's how Ive done it. In this example C1will contain a single digit representing the # seconds so that I can control it from the sheet rather than editing the macro. If you just want to hard code the 5 seconds you can set Freq="00:00:05" or get rid of that line and edit the next one to

TimeToRun = Now + TimeValue("00:00:05")

There are 2 macros involved ....

the first sets the time period to run the routine you want and then "calls" that macro
the second is the routine you want to run every 5 seconds. The final line in this macro is to call the first macro to start the timer again
Code:

[B]Sub ScheduleCopyPriceOver[/B]()
Freq = "00:00:0" & Range("C1")
TimeToRun = Now + TimeValue(Freq)
Application.OnTime TimeToRun, "[B][COLOR=red]CopyPriceOver[/COLOR][/B]"
[B]End Sub[/B]
'
[B]Sub CopyPriceOver[/B]()
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Do what you need to do
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Call [B][COLOR=red]ScheduleCopyPriceOver[/COLOR][/B]
[B]End Sub[/B]
Finally, if you want to start the timer when the the file is opened you can add another macro like this
Code:
Sub auto_open()
Call ScheduleCopyPriceOver
End Sub
All of these macros can be in a normal module.
 

Read other 2 answers
RELEVANCY SCORE 51.2

Hello,

I am trying to write a code for this workbook that will generate an email to my outlook account when the file is modified in anyway. I am not sure why my code is not working and welcome any assistance!

Additionally, it would actually be nice if the code were setup so that the email contained the information in columns B-F and was only generated when one of these columns is edited.

Thanks in advance!!

Ps. I tried to upload the file from my computer, but am getting a message that I am either not logged in/do not have access.
 

A:Solved: excel email to outlook code help

Read other 15 answers
RELEVANCY SCORE 51.2

one more little problem.

I have this code (which I got from recording a macro) for placing a chart on a page:

Range("C920").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("FixedPointsBlank").Range("C920"), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "=FixedPointsBlank!R9C3:R20C3"
ActiveChart.SeriesCollection(1).Name = "=""Frequency"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="FixedPointsBlank"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Frequency Plot for Fixed Measurements"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"Upper End Value (cpm)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Frequency"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False

On the 2 lines that have the Range function I have replaced inside the parenthesis with: Cells(30, 8), Cells(lastUEV, 9)
which works just fine. I have to do this because from one run to another the range size will change.

The other line that needs to be changed the same way is:

ActiveChart.SeriesCollection(1).XValues = "=FixedPointsBlank!R9C3:R20C3"

However, when I replace the R9C3:R20C3 with Cells(30, 8), Cells(lastUEV, 9) I get an App... Read more

A:Solved: excel vb macro code for chart

Read other 10 answers
RELEVANCY SCORE 51.2

Hi I am trying to create a macro in excel that will perform a number of formatting tasks in preparation for the csv file to be imported into another database. I can't seem to get the macro to delete all of the rows containing text (specifically "student(number)") instead of numbers. It seems to work for some but in my test file I always have 4 rows remaining. It also doesn't seem to work on the first row of the worksheet. In the code below I am referring to the section that is commented with 'Find text in column A and delete row. Any assistance would be greatly appreciated.

Sub LMSImport()
'
' LMS Import Macro
' Prepare csv file for import to LMS

Dim wscell As Range
Dim lastrow As Long
Dim username As Range

'select last 2 columns and delete
Range("H:I").EntireColumn.Select
Selection.delete Shift:=xlToLeft

'Find text in column A and delete row
For Each username In Range("A:A")

If username.Value = Empty Then
Exit For
End If

If username.Value Like "*student*" Then
username.Rows("1:1").EntireRow.Select
Selection.delete Shift:=xlUp
End If
Next

' Insert e-learning into column G
lastrow = Range("G1").End(xlDown).Row
Range("G1").Select
ActiveCell.FormulaR1C1 = "e-Learning"
Range("G1").Select
Selection.autofill Destination:=Range("G1:G" & lastrow&)

' select column D clear and convert column C to uppercase
lastrow = Range(... Read more

A:Solved: Excel vb code delete row containing text in A:A

It seems to work for some but in my test file I always have 4 rows remaining. It also doesn't seem to work on the first row of the worksheet.
Click to expand...

The code you've marked in bold will delete the top row (1:1) on condition stated is true; You've not stated why you're doing this, and I'm not sure this is what you intend.

I'm second guessing that you want to delete the rows which meet your condition rather than the top row, in which case replace the your bolded code with:
Code:

'Don't for get to dimension the variable 'x' at start of your code

For x = Cells(Application.Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(x, "A") Like "*student*" Then
Rows(x).EntireRow.Delete
End If
Next




HTH
 

Read other 3 answers
RELEVANCY SCORE 51.2

Hi,
My cryptic title means the following.
I have the following pop-up calendar code to fill a particular field in a userform:
Option Explicit
' ===================================================
' Code by Martin Green eMail [email protected]
' Visit my Office Tips web site at www.fontstuff.com
' ===================================================
Private Sub cmdClose_Click()
' Close the UserForm
Unload Me
End Sub
Private Sub UserForm_Initialize()
' Check if active cell contains a date. If 'yes' show
' same date on calendar. If 'no' show today's date.
If IsDate(MedewForm.ActiveControl ) Then
Calendar1.Value = DateValue(MedewForm.ActiveControl )
Else
Calendar1.Value = Date
End If
End Sub
Private Sub Calendar1_Click()
' Transfer date selected on calendar to active cell
' and close UserForm.
MedewForm.ActiveControl = Calendar1.Value
Unload Me
End Sub

I would like to use this form for more than one form.
I imagine I could store the active userform name in a variable and replace MedewForm with this variable and then use it for any number of userforms but:
HOW DO YOU DO IT?

I have tried declaring a public constant but that did not work.
Any suggestions will be appreciated.

 

A:Solved: VBA (Excel) code one userform for more forms

I found the solution.
Remove the userformname, in my case MedewForm, and it works with any form.
Tested it and it works.
 

Read other 1 answers
RELEVANCY SCORE 51.2

Hi -

I am trying to create a new 'number format code' in the following format:

hh:mm:ss;ff

as timecode (where hh=hours, mm=minutes, ss=seconds, ff=frames)

I had no luck so far. I tried starting from a hh:mm:ss template and adding the ";ff" that I need at the end, but when I enter a number in the formatted cell, all I get is 00:00:00
Any ideas?
Thank you.
 

A:Solved: Excel Number Format Code

I'm pretty sure Excel custom formats don't support frames - ' f ' is an invalid character for a custom format and so it is treated as just text. To display the semicolon, precede it with a backslash. I think the closest you'll get is

00\:00\:00\;00

This will essentially take care of the punctuation for you (colons and semi colons) but you will need to punch in all 8 digits yourself during data entry.
 

Read other 2 answers
RELEVANCY SCORE 51.2

I have a Excel 2007 workbook that list products, quantity, and sold. Is there a way to have a formula or some type of code to determine the top ten best selling products?
 

A:Solved: Excel 2007 Formula/Code

Read other 16 answers
RELEVANCY SCORE 50.8

Hi all,
I have a worksheet that I use fairly regularly that was originally created in Excel2003 (or maybe even 2000). I have recently upgraded to office 2010 and now when I run the code I get an error in one of the subs.
Specifically; "Run-Time Error '1004':
Excel cannot insert the sheets into the destination workbook because it contains fewer rows and columns than the soruce workbook..."

Here is the sub where it fails;

Sub GetCumPlt()
... Selects sheet in existing workbook and deletes it.

Workbooks.OpenText Filename:="cum.plt",... populates worksheet with data.

Sheets("cum"). Select
Sheets("cum"). Move Before:=Workbooks("timeline.xls").Sheets(3)

I have looked in the 2010 VBA help but from what I can see the syntax is still valid.

Any suggestions?

Thanks
 

A:Solved: Running Excel 2003 code in Excel2010

An excel 2007 and above sheet is 1048576 rows and 16384 columns.
When you do Sheets("cum").select, this amount of cells is being copied.
However, you try to move this into excel 2003 which has only 65536 rows by 256 columns.

You either create a new sheet in the excel 2003 file and copy only the used range over.
Or convert timeline.xls to timeline.xlsx(m) which is an excel 2010 format.
 

Read other 2 answers
RELEVANCY SCORE 50.8

Hi

I currently have a worksheet called "Plan" where the user will enter text into "C34", once complete they will click on a form control button to email the spreadsheet.

I need help with code that would "copy" the text from "C34" to a worksheet named "Goal 1", position "C8" when the user clicks the form control button.

Any help would be greatly appreciated.
 

A:Solved: Excel Copy text code help needed :-)

Decided to use a formula instead ;-)

=IF(sheet1!A1<>"",sheet1!A1,"")
 

Read other 1 answers
RELEVANCY SCORE 50.8

Good evening,
I have just switched from Excel 2003 to 2007 where in 2003 this code was working and now it doesn’t. I tried to select all and to cut/paste the info for a refresh, but nothing.
Here is what I need to do, I will attach my workbook.
In tab (sheet4) “Contracts” I have information in Column A (starting at row5), in this case the word “Testing”. When I insert a number in that same row but in column B it will copy the “Testing” from column A and add /001/HS to column C but will also insert this information to the next row if a higher number is inserted in Column B. Example is to insert 003 in column B5. It will show you a little what I am trying to do.
Now I had an automatic code that would bypass the rest and add the same number inserted in Column B to my next tab (sheet5) “Report” to the same row matching the word from Sheet4 “Contracts” to sheet5 “Report”. So if you add 003 to Column B in the “Contracts” sheet it would copy the same number in Sheet5 “Report” in column L on the same row of the “Testing” in column A. So a matching code can be found on top to the VBE in each sheet.
It’s hard to explain but once you do a test in my Sheet4 “Contracts” you will understand a little more.
Now from 2003 to 2007 this code has stopped working and I cannot figure out why.
I need your help. Please, this is bugging me and I don’t understand.

I read the code and it looks good and should function as it was intended to...
 

A:Solved: Excel 2007 Transfer help with a matching code

Thank you but I have found my error

Sometime the only thing you need is a jolt of coffee

Thank you,
 

Read other 1 answers
RELEVANCY SCORE 50.8

I really feel that this should be easier than it is turning out to be. I'm using an Access data base to track when boxes of files are closed and when it is time to destroy them. This code is supposed to update the table in the database with the Closure Date and the Burn Date but i keep getting a Run-time Error '-2147217900 (80040e14)' Error in UPDATE code and I just dont see it. Maybe I just need a second pair of eyes. Thanks
Here's the code:

Sub UpdateBox()

Dim cnt As adodb.Connection
Dim rst1 As adodb.Recordset
Dim stDB As String, stSQL1 As String
Dim stConn As String
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet
Dim lnField As Long, lnCount As Long
Dim B As Integer
'Instantiate the ADO-objects.
Set cnt = New adodb.Connection
Set rst1 = New adodb.Recordset
Set wbBook = ThisWorkbook
Set wsSheet1 = wbBook.Worksheets(2) 'Box
B = CInt(CloseBoxForm.TextBox1.Text)

'Path to the database.
stDB = "\\sdrol201\DATA\REGION4\Case Closure DB\Closed_Case.mdb"

'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & stDB & ";"

'The 1st raw SQL-statement to be executed.
stSQL1 = "UPDATE tblBox SET ClosureDate = " & Date & ", BurnDate = " & Date + 1096 & "WHERE BoxNumber= " & B & ";"
' "SELECT tblBox.BoxNumber FROM tblBox WHERE BoxNumber = " & B & ";"... Read more

A:Solved: HALP!! Excel code to update Access DB

In the VB editior click VIEW >> IMMEDIATE WINDOW to show the debug window. Then output your SQL statement to this window by adding the line of code below prior to the statement execution. Copy the result and paste into your next post.
Code:
Debug.Print stSQL1
Regards,
Rollin
 

Read other 3 answers
RELEVANCY SCORE 50.8

In a database exported to Excel, a number of records come in for multiple sales people. Each row is a record. However the sales person's name in Column A is only shown once and for each of his records below that, the cells in column A are blank until the first record of the next salesperson, where his name is shown only once, then all his records are shown in subsequent rows, but cells below HIS name are blank (they do not repeat his name) until the first record of the next salesperson, and so on.

In order to sort these records I need to autofill the blank cells below EACH salesperson's name down to where the next salesperson's name first appears, and then autofill the next salesperson's name down to the next one, etc.

This step is part of a larger macro, but I have to end the macro there and do this manually because I can't figure out how to write the code to do it automatically. ??? Hope someone can help!

Thanks, Jim
 

A:Solved: Need Excel Macro Code to Autofill Column

Read other 12 answers
RELEVANCY SCORE 50.8

Hi,
Can someone help me with vba code in Exel?
I have several sheets which contain all the code I need to do some calcumations based upon an input log.
Once a month the calculated data is written to cumulative sheet on the network. I have code that checks if the sheet is in use to avoid the error when trying to update it.
What I need is the code line to retrieve the name of the user (%username%) that is currently using it.

We are not allowed to use net send message to a username so if somebody has code that can cause a pop-up messag to show on that user's screen, then it's even better.

Thanks for the effort.
 

A:Solved: Excel VBA code: Get username of an opend file

Read other 9 answers
RELEVANCY SCORE 50.8

Hi. I am trying to run a macro to copy items on 1 sheet and have it paste them on the 2nd and 3rd sheet. I am trying but can't quite seem to get the excel code to do what I want. I can't post a copy of it because I accidentally deleted it.

Can anyone help me?
 

A:Solved: Excel: Copy/Paste Macro Code Help

Read other 9 answers
RELEVANCY SCORE 50.8

Has anyone ever seen the error message "Code execustion has been interrupted" in Excel 2007?
 

A:Solved: Excel 2007 Code Execution Error

Read other 6 answers
RELEVANCY SCORE 50.8

I am writing a food costing program using excel. The program is designed to pull prices from various tabs(different suppliers) to a main Supplier Costing Page containing all the information on products for all suppliers. The code allows the user to enter in the name of the product, then uses the LOOKUP function to search for the product and returns the value in that row. The code I used to pull the information from the different suppliers works properly:

A19 value Steak Strip Loin C/C St. Silver FRSH

=LOOKUP(A19,Centennial!$A$6:Centennial!$A$100,Centennial!$R$6:Centennial!$R$100)

Centennial is the tab for that supplier. It pulls the correct value from the page 0.74, to cell R19
The problem I am having is that when I try to use this format of code on another tab it does not return the correct value:

=LOOKUP(A33,'Supplier Costing'!$A$6:'Supplier Costing'!$A$100,'Supplier Costing'!$R$6:'Supplier Costing'!$R$100)

The code should return the same value of the previous line of code as it is the same product:0.74 to cell R33 this time, however it returns the value of another product on the Supplier Costing tab. Is the LOOKUP function not searching the tab for the whole string? Any help or suggestions would be extremely helpfull. Thanks
 

A:Solved: Excel Programming Code Produces Wrong Value

Read other 12 answers
RELEVANCY SCORE 50.8

Hello,
I'm trying to enter data into cell D2 in excel 2007 using a form and a VBA combobox (on the form) to do it. The combobox gives me two fixed choices and I also want to enter a number of my own choosing. I have no problem with the two fixed choices which are entered using array(, 10000, 100000) I have the first array amount 'blank' so I can enter my own number. I have tried DIM myValue as Integer and array(myValue, 10000,100000), etc. no good. When I place a number in the first drop down line which is blank and hit 'ENTER' nothing happens, although I suspect a blank was entered in D2.

This is what I currently have. This macro is just a test macro to get the code working. I will splice it in my real macro when I get it working. I have Initialize code, etc. to get to this piece of code. Works OK.

Private Sub UserForm_Initialize()

ComboBox1.List = Array(MyValue, 10000, 100000)
Range("A2").Value = ""
Range("A4").Value = ""
Range("A6").Value = ""
Range("A8").Value = ""
Range("A10").Value = ""
Range("D2").Value = ""
UserForm1.Show
End Sub
======The other Range items above are check box values being reset on the same userform

Private Sub ComboBox1_Change()
Dim MyValue As Integer

If ComboBox1.Value = MyValue Then
Range("D2") = MyValue << this dosen't work, everything below does work
ElseIf ComboBox1.Value = 100... Read more

A:Solved: Excel 2007 combobox code problem

Welcome to the forum.
It's in the definiton of the combo box, you can either set it to allow only numbres from list, or to all values.
Check the properties for the combo box in the form
 

Read other 2 answers
RELEVANCY SCORE 50

I would like to know if anyone has a simple macro that returns a true if a selected range is not contiguous.

The idea is, I have a workbook with one sheet for every month where tasks, vacation etc are kept and planned.
You can select a range or several ranges (see attached bmp file) and usign the Right mouse button it permits the user to enter a 'V' for vacation in that selected range or ranges

What I am looking for now is if in case the range is not contiguos I want to be able to determine where each different range starts and ends (that I can do with some code I already have for a contiguous range) but now I would like the code to return the three anges

"A4:B5" "C6:E7" "F8:J10"

Then I execute the appropiate code to enter, format or whatever each cell in the separate ranges, the actions will be same but skippng the not selected cells.

Maybe something like not in selection?

It's difficult to attach the workbook I'm using since I don't have an empty one at hand right now, but I'm preparing one.

It's easier to prgram than to explain what I want.

Thanks in advance for any tip
 

A:Solved: Excel: need vba code to determine if selected range is contiguous

Just for the info, I FOUND IT!

I found the following code: it selects one cells of the cells in de selected non cntiguous range

Sub UnSelectActiveCell()
Dim Rng As Range
Dim FullRange As Range

If Selection.Cells.Count > 1 Then
For Each Rng In Selection.Cells
If Rng.Address <> ActiveCell.Address Then
If FullRange Is Nothing Then
Set FullRange = Rng
Else
Set FullRange = Application.Union(FullRange, Rng)
End If
End If
Next Rng

If FullRange.Cells.Count > 0 Then
FullRange.Select
End If
End If
End Sub

Now I added my code to loop until count < 1

Sub Unselectthem()
Do While Selection.cells.Count > 1
UnSelectActiveCell
Loop
Range("B5").Select
End Sub

and in the first one I will replace the red text with my code

If FullRange.Cells.Count > 0 Then
FullRange.Select <<< here my code to invoke the necessary module
End If

I just added this, maybe somebody else is / was looking for something similar.

Thanks anyway.
 

Read other 1 answers
RELEVANCY SCORE 50

Hello,
I would like some assistance in populating the city and county from one worksheet to another worksheet with a list of zip codes. I have attached my worksheet for you to look at.

Much appreciation for your help
 

A:Solved: City and County Search for Zip Code in Excel 2002

Read other 12 answers
RELEVANCY SCORE 49.6

Hi,

This is still carrying on the reference of my last issue for creating a calender format worksheet for my workplace, which was solved here:

http://forums.techguy.org/business-applications/867177-solved-formula-find-date-enter.html

thanks to MRdNK for the time taken out to solve the problem and to Aj_Old for the useful insight much needed in the end.

The gist of what was to be done was that I was suppose to take the input from the user on the first sheet and repectively update the second sheet which has been formatted to look like a calender to update accordingly and display the specific events happening on that very day. The third sheet then summeries the events weekly and displays a count as to how much is happening in a week.

Now the problem that has come us is with conditional formatting. I have to divide the tasks happening into 4 groups and highlight the dates through these ranges.
For Example:
The time taken for the brief about a project suppose starting on the 1st and ending on the 6th needs to be shown in Green all throughout ie all dates between 1st and 6th for that project need to be green.
The time taken to work similarly needs to be in Yellow.
The time taken for corrections and finalizations needs to be in blue.
And the final delivery date in Red.

I know that this can be achieved with VB Coding but am clueless as to where to start as I have not done VB coding for almost 3 years now and am having a hard time thinking up the code. (Am familiar with the lan... Read more

A:Solved: VB Code to overcome the Conditonal Formating limit of 3 in Excel 2003

Read other 12 answers
RELEVANCY SCORE 49.6

Hi again,

This one should be much easier than my last issue ...

I'm writing excel macros and everything is going great except that when i tell my macro to save a particular file i need it to add that day's date (the current date) to the filename...

Any takers?

LTT

Oh yeah, Merry Christmas and Happy Holidays to everyone at Tech Support Guys!!
 

A:Solved: Excel 2003 VBA code to Auto Date file names?

Read other 7 answers
RELEVANCY SCORE 49.6

Hi

I recorded a Macro to copy the First two Rows and some cells of a draft sheet into various other sheets.
The Problem is, that it records "Windows("Wunderli Rainer Kundenliste 2010 TEST.xlsx").Activate", when I move tback to the sheet where I want to insert the data. I want it to work for every sheet with different names. Is there a possibility to modify the macro so that it works for any sheet independent of the name? Like an order "go back to previous sheet?"
It looks like this:
Code:
[FONT=Calibri][SIZE=3]Sub KopiereVonVorlageFormelnUndSpalten()[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]'[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]' KopiereVonVorlageFormelnUndSpalten Macro[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]'[/FONT][/SIZE]

[SIZE=3][FONT=Calibri] Windows("0_AAA_Vorlage für Kundenlisten.xlsx").Activate[/FONT][/SIZE]
[SIZE=3][FONT=Calibri] ActiveWindow.WindowState = xlNormal[/FONT][/SIZE]
[SIZE=3][FONT=Calibri] ActiveWindow.WindowState = xlNormal[/FONT][/SIZE]
[SIZE=3][FONT=Calibri] Rows("1:2").Select[/FONT][/SIZE]
[SIZE=3][FONT=Calibri] Range("A2").Activate[/FONT][/SIZE]
[SIZE=3][FONT=Calibri] Selection.Copy[/FONT][/SIZE]
[SIZE=3][FONT=Calibri] Windows("[COLOR=red]Wunderli Rainer Kundenliste 2010 TEST.xlsx[/COLOR]").Activate[/FONT][/SIZE]
[SIZE=3][FONT=Calibri] Rows("1:2").Select[/FONT][/SIZE]
[SIZE=3][FONT=Calibri] Range("A2").Activate[/FONT][/SIZE]
[SIZE=3][FONT=Calibri] Act... Read more

A:Solved: Excel 2007 VBA code to copy the first rows of a draft to various sheets

Read other 16 answers