Re: Making application invisible
Hello thelocaluk:
You should take a look at Dave Hawley's presentation, entitled Creating a Splashscreen for your Excel Workbook at http://www.ozgrid.com/Excel/excel-splash-screen.htm.
Hope This Helps,
Chris
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
Re: Making application invisible
Hello thelocaluk:
You should take a look at Dave Hawley's presentation, entitled Creating a Splashscreen for your Excel Workbook at http://www.ozgrid.com/Excel/excel-splash-screen.htm.
Hope This Helps,
Chris
Re: Find method: using one range to search for all occurences in another
Thanks Guys!
I"ve been having a fit with this. And it seems the more I fiddled with this the more that it deteriorated. I will try both suggestion and let you know they turn out.
Thanks Again,
Chris
Hello All:
I trying to use the find method with two ranges. My objective is to locate all occurences of the values of range 1 in range 2. My macro finds all the values, however, it loops to many times. Here is a copy of my code. Any help with this problem will be greatly appreciated.
TIA,
Chris
Dim NextRow3 As Long
Dim NextRow As Long
Dim foundCell As Range
Dim Cell As Range
Dim rng As Range
Dim rng3 As Range
Dim tempCell As Range
Dim iLoop As Integer
Dim rng3Count As Long
Dim rng3Count1 As Long
On Error Resume Next
Application.ScreenUpdating = False
Set rng = Sheet2.Range("A7:A" & Sheet2.Range("A65536").End(xlUp).Offset(0, 0).Row) 'Range on Emission Process Form
Application.ScreenUpdating = False
Set rng3 = Sheet20.Range("A7:A" & Sheet20.Range("A65536").End(xlUp).Offset(0, 0).Row) 'Range on Emission Form Back-Up
'Counts SCC Values that match TextBox1.Value
Application.ScreenUpdating = False
For Each Cell In rng
If Cell.Value = "" Then
GoTo 100
ElseIf Cell.Value <> "" Then
GoTo 40
End If
40
Set foundCell = rng3.Find(What:=Cell.Value, LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False) 'Find Method employed to match TextBox1.Value
Application.ScreenUpdating = False
NextRow3 = Sheet5.cells(Rows.Count, "a").End(xlUp).Offset(1, 0).Row 'Offset(1,0)will locate next empty row on Emission Form, column a
Sheet5.cells(NextRow3, 1) = foundCell.Value 'Writes EUID to Emission Form
Sheet5.cells(NextRow3, 2) = foundCell.Offset(0, 1).Value 'Writes EP to Emission Form
Sheet5.cells(NextRow3, 3) = foundCell.Offset(0, 2).Value 'Writes Pollutant Code to Emission Form
Sheet5.cells(NextRow3, 5) = foundCell.Offset(0, 4).Value 'Writes ERP to Emission Form
Sheet5.cells(NextRow3, 6) = foundCell.Offset(0, 5).Value 'Writes Start Date to Emission Form
Sheet5.cells(NextRow3, 7) = foundCell.Offset(0, 6).Value 'Writes End Date to Emission Form
Sheet5.cells(NextRow3, 8) = foundCell.Offset(0, 7).Value 'Writes Start Time to Emission Form
Sheet5.cells(NextRow3, 9) = foundCell.Offset(0, 8).Value 'Writes End Time to Emission Form
Sheet5.cells(NextRow3, 11) = foundCell.Offset(0, 10).Value 'Writes Emissions Values to Emission Form
Sheet5.cells(NextRow3, 12) = foundCell.Offset(0, 11).Value 'Writes Emission Units to Emission Form
Sheet5.cells(NextRow3, 13) = foundCell.Offset(0, 12).Value 'Writes Emission Type to Emission Form
Sheet5.cells(NextRow3, 14) = foundCell.Offset(0, 13).Value 'Writes Emission Factor Numeric Values to Emission Form
Sheet5.cells(NextRow3, 15) = foundCell.Offset(0, 14).Value 'Writes Emission Factor Numerator Units to Emission Form
Sheet5.cells(NextRow3, 16) = foundCell.Offset(0, 15).Value 'Writes Emission Factor Denominator Units to Emission Form
Sheet5.cells(NextRow3, 17) = foundCell.Offset(0, 16).Value 'Writes Material Code to Emission Form
Sheet5.cells(NextRow3, 18) = foundCell.Offset(0, 17).Value 'Writes Material I/O Code to Emission Form
Sheet5.cells(NextRow3, 19) = foundCell.Offset(0, 18).Value 'Writes Material Description to Emission Form
Sheet5.cells(NextRow3, 21) = foundCell.Offset(0, 20).Value 'Writes Emission Calc Method Code to Emission Form
Sheet5.cells(NextRow3, 22) = foundCell.Offset(0, 21).Value 'Writes Control Status to Emission Form
Sheet5.cells(NextRow3, 23) = foundCell.Offset(0, 22).Value 'Writes Emission Data Level to Emission Form
If foundCell Is Nothing Then
GoTo 50
End If
rng3Count = WorksheetFunction.CountIf(rng3, Cell.Value)
Application.ScreenUpdating = False
For iLoop = 1 To rng3Count - 1
iLoop = iLoop + 0
Set tempCell = foundCell
Set foundCell = rng3.FindNext(After:=tempCell)
'NextRow will locate next empty row on the specified sheet
Application.ScreenUpdating = False
NextRow3 = Sheet5.cells(Rows.Count, "a").End(xlUp).Offset(1, 0).Row 'Offset(1,0)will locate next empty row on Emission Form, column a
Sheet5.cells(NextRow3, 1) = foundCell.Value 'Writes EUID to Emission Form
Sheet5.cells(NextRow3, 2) = foundCell.Offset(0, 1).Value 'Writes EP to Emission Form
Sheet5.cells(NextRow3, 3) = foundCell.Offset(0, 2).Value 'Writes Pollutant Code to Emission Form
Sheet5.cells(NextRow3, 5) = foundCell.Offset(0, 4).Value 'Writes ERP to Emission Form
Sheet5.cells(NextRow3, 6) = foundCell.Offset(0, 5).Value 'Writes Start Date to Emission Form
Sheet5.cells(NextRow3, 7) = foundCell.Offset(0, 6).Value 'Writes End Date to Emission Form
Sheet5.cells(NextRow3, 8) = foundCell.Offset(0, 7).Value 'Writes Start Time to Emission Form
Sheet5.cells(NextRow3, 9) = foundCell.Offset(0, 8).Value 'Writes End Time to Emission Form
Sheet5.cells(NextRow3, 11) = foundCell.Offset(0, 10).Value 'Writes Emissions Values to Emission Form
Sheet5.cells(NextRow3, 12) = foundCell.Offset(0, 11).Value 'Writes Emission Units to Emission Form
Sheet5.cells(NextRow3, 13) = foundCell.Offset(0, 12).Value 'Writes Emission Type to Emission Form
Sheet5.cells(NextRow3, 14) = foundCell.Offset(0, 13).Value 'Writes Emission Factor Numeric Values to Emission Form
Sheet5.cells(NextRow3, 15) = foundCell.Offset(0, 14).Value 'Writes Emission Factor Numerator Units to Emission Form
Sheet5.cells(NextRow3, 16) = foundCell.Offset(0, 15).Value 'Writes Emission Factor Denominator Units to Emission Form
Sheet5.cells(NextRow3, 17) = foundCell.Offset(0, 16).Value 'Writes Material Code to Emission Form
Sheet5.cells(NextRow3, 18) = foundCell.Offset(0, 17).Value 'Writes Material I/O Code to Emission Form
Sheet5.cells(NextRow3, 19) = foundCell.Offset(0, 18).Value 'Writes Material Description to Emission Form
Sheet5.cells(NextRow3, 21) = foundCell.Offset(0, 20).Value 'Writes Emission Calc Method Code to Emission Form
Sheet5.cells(NextRow3, 22) = foundCell.Offset(0, 21).Value 'Writes Control Status to Emission Form
Sheet5.cells(NextRow3, 23) = foundCell.Offset(0, 22).Value 'Writes Emission Data Level to Emission Form
If tempCell.Row >= foundCell.Row Then
Exit For
End If
Next iLoop
Next Cell
50
100
Display More
Hi All:
VBA is great but it sure can be perplexing.
I am working on a project and I need some VBA-code to perform a "find" then a "findnext" using a userform. Does anyone have something along these lines that I can take a gander at see where I'm going wrong?
In the code that I have used the variable that should receive the value to implement the "findnext" is not being detected.
Any help I can get on this is greatly appreciated.
code example:
-------------------
Private Sub FindIt_Click()
ActiveSheet.Protect UserInterfaceOnly:=True, password:="engineer"
Dim foundCell As Range
Dim foundValue As Range
Dim searchrng As Range
Dim textToBeFound As String
textToBeFound = TextBox5.Value
Set searchrng = Sheets("Codes").Range("B773:B2638")
'Find SIC Description and code along with NAICS description and code from input in "Find It" box
With searchrng
Set foundValue = .Cells.Find(What:=textToBeFound, _
after:=(.Cells(.Cells.Count)), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
FindIt.Caption = "Next>>"
CommandButton2.Caption = "<<Previous"
Set foundCell = foundValue
Set foundValue = searchrng.FindNext(after:=foundCell)
'test to see if any matching text was found using an IS test
If foundValue Is Nothing Then
MsgBox "No Match Found! Either refine your description and try again, or consult the Source Industrial Code (SIC) table for the appropriate description."
Exit Sub
End If
End With
TextBox8.Value = foundValue 'loads SIC Description
TextBox6.Value = foundValue.Offset(0, -1) 'loads SIC Code
ActiveSheet.Range("f14").Value = foundValue.Offset(0, -1) ' loads search variable for "Next" and "Previous" arguments
TextBox9.Value = foundValue.Offset(0, 2) 'loads NAICS Description
TextBox7.Value = foundValue.Offset(0, 1) 'loads NAICS Code
End Sub
TIA,
Chris:(:(:(
Thanks Guys:
Thanks you so much for your assistance, I really appreciate your help. Things are finally beginning to look up.
I have implemented some of the code that you suggested and I am now able to initiate a search, retrieve both 2 descriptions and 2 codes to the userform simultaneously, and assign the found code values to their intended destination cells on the spreadsheet. But I have yet to get the application to perform a "next" or "previous" search.
In my application the userform queries the end-user for input, performs a search which is designed to retrieved 2 corresponding codes along with their descriptions. This allows the user determine if he has obtained the appropriate code.
Here is a copy of the code:
-------------------------------------
Private Sub FindIt_Click()
Dim foundCell As Range
Dim foundValue As Range
Set foundCell = foundValue
'Find SIC Description and code along with NAICS description and code from input in "Find It" box
200 Set foundValue = Sheets("Codes").Range("b773:b2638").Find(What:=TextBox5.Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
FindIt.Caption = "Next>>"
'test to see if any matching text was found using an IS test
If foundValue Is Nothing Then
MsgBox "No Match Found! Either refine your description and try again, or consult the Source Industrial Code (SIC) table for the appropriate description."
Exit Sub
Else 'If text found, go to cell where text found
TextBox8.Value = foundValue 'loads SIC Description
TextBox6.Value = foundValue.Offset(0, -1) 'loads SIC Code
TextBox9.Value = foundValue.Offset(0, 2) 'loads NAICS Description
TextBox7.Value = foundValue.Offset(0, 1) 'loads NAICS Code
End If
End Sub
---------------------------------------
How do I format the "After" argument being that my values are being sent to the userform?
Hi:
I am in the process of developing a custom userform. I want it to appear very much like the standard "find" userform provided in Excel, however, than are a few exceptions. First, I want this userform to be capable of both next and previous searchdirections. Then, I want it to report the result of the find on the userform. Thus, far I my search starts fine but it will not report the value to the list box. Should I being using a listbox or a textbox?
Anyway, here is a copy of the code:
Sub listbox1_Click ()
Dim foundvalue as Variant
listbox1.value = foundvalue
End Sub
Is this code sufficient? HELP!!!
TIA,
Chris:(
:question:
Hi All,
I have a custom application and I would like to create a custom desktop icon, as well as, replace the "Excel" icon to the left of the Microsoft Excel window caption. Is this possible and does anyone know how to accomplish this? If so, can you offer a code example?
Thanks,
Chris
Hi All:
I need help writing a macro. Here's my problem, I would like to get the maximum value of each range a column. For instance, I have two columns of data, column A contains sensor numbers, while column B contains sensor temperature readings, like so
column A............. column B
sensor #............sensor temp. (F)
N2 ...................... 100
N2 ...................... 115
N2..........................90
S3........................120
S3........................115
W4........................119
E1........................100
E1........................111
E1........................112
......and so.
I want a macro that takes into consideration that sensor numbers N2, S3, W4, and E1 entered in column A are all separate and distinct ranges. Once the ranges are is established I would like the macro to determine the maximum value of each range, report that value in a message box and change the background color of the cell.
Here is the code that I have come up with:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Sub Get_Max_Value_of_Ranges()
Dim rnga, rngb, Max, Rng, As Range
Dim i as Integer
Set rnga = ActiveSheet.Range("A7:A100")
Set rngb = ActiveSheet.Range("B7:B100")
For i = 7 to 100
Set Rng = Intersection(rnga, rngf)
Set MaxRng = Rng.Cells(Application.Match(Application.Max(Rng), Rng, 0)
Msgbox "Max. Value for" & rnga & "=" & MaxRng
MaxRng.Interior.ColorIndex = 3
Set rngf = rngf.Resize(rngf.rows.Count + 1, rngf.Columns.Count)
Next i
End Sub
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Thanks in advance for any help.
Chris
Hi ALL,
I posed this question yesterday and thought that it was resolved, however, the solution opened the door to new problems.
My project incorporates about a half dozen or more columns (each column with at least 240 rows) which run a macro using the find method when the user double-clicks a cell.
Currently, I'm running this code:
>>>>>>>>>>>>>>>>>>>>>
IPrivate Sub Worksheet_BeforeDoubleClick( _
ByVal target As Excel.Range, _
Cancel As Boolean)
Dim addr As String
addr = target.Address
If addr = "$A$13" Then
End If
If addr = "$A$14" Then
End If
If addr = "$A$15" Then
End If
and so on................
End Sub
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
and it works spendidly. The problem with this code is that it includes a reference for each cell where I want this "Double-Click" event to occur, thus it uses too much memory.
I recently experimented with variations of this code that would be more efficient and reduced the amount of memory required to run the procedure. The code included the following:
code:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>CaIPrivate Sub Worksheet_BeforeDoubleClick( _
ByVal target As Excel.Range, _
Cancel As Boolean)
Dim addr As String
addr = target.Address
If addr >= "$A$13" and addr <= "$A$100" Then
Call 'FindRoutine'
End If
End Sub
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
The above code produces inconsistent results, sometimes it works and other times not at all.
I also tried the following and at first I thought I had my problem resolved, but then I notice that while my routine would work in column "A", it would work before cell "$A$13" even though my control statement clearly defines the range where it should be activate.
code:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Private Sub Worksheet_BeforeDoubleClick( _
ByVal target As Excel.Range, _
Cancel As Boolean)
Dim addr As String
addr = target.Address
Select Case addr
case "$A$13" To "$A$100"
Call 'FindRoutine'
End Select End Sub
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Furthermore, I have tried to define my range with:
Case Is >="$A$13"
again the results are inconsistent.
Any help onthis will be greatly appreciated.
Chris
Hi Anonymous:
Try this, on the toolbar select tools - options - view and then deselect zero values. I hope this helps.
Chris
Hi all,
My project has ballooned and it is now returning "Out of Memory" errors. I have deleted all unnecessary code and worksheets. Can anyone give me any suggestions of other things to try.
My biggest memory hogs are the half dozen or more columns (each column with at least 240 rows) which run a macro using the find method when the user double-clicks a cell.
Currently, I running this code:
>>>>>>>>>>>>>>>>>>>>>
IPrivate Sub Worksheet_BeforeDoubleClick( _
ByVal target As Excel.Range, _
Cancel As Boolean)
Dim addr As String
addr = target.Address
If addr = "$A$1" Then
End If
If addr = "$A$2" Then
End If
If addr = "$A$3" Then
End If
and so on................
End Sub
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
and it works spendidly. My problems is that my code includes a reference for each cell where I want this "Double-Click" event to occur, thus I have created a memory hog.
Can this routine be altered to give the same result with less code?
Can looping be incorporated with this code?
Can any give an example of some code
that acheives the desire goal by looping through these statements?
Help Please!
Chris:puzzled:
Hi Denis,
The code I dsplayed works fine. I need help modifying it to yield the following results: agents 1 thru agents 100 are all in column 9, the date of sale is in column 10, and the amount sold is in column 12. I want to highlight all the maximum sales values for each agent. Whereas, the code that I displayed is highlighting one singular maximum value.
Looping will be required, but how do I start?
Again, any help at all will be greatly appreciated.
Thanks,
Chris
Hi bt,
No I have not, according to Dave Hawley in "MS Excel Visual Basic Examples" that syntax can return errors. He suggest dropping the 'Worksheets'.
Actually the code that I displayed works fines, I simply need it to do more. In it's present state it highlights "the maximum value" in the column. I need it to hightlight each maximum sale value for each Agent on a monthly basis.
Thanks,
Chris:puzzled:
:puzzled:
Hi all,
Can someone help me with the vba code to find the maximum value in a column. My spreadsheet incorporates several columns Agent Number, Date, and Sales are the main concerns. I want an automatic procedure where my macro hightlights the maximum sales of each agent. How can adapt the following macro to suit my needs.
******************************
code:
Sub Find_Max()
Dim searchRange As Range
Dim cell As Range
Dim maxValue, maxRow As Integer
Set searchRange = Columns("L")
maxValue = Application.Max(searchRange)
maxRow = Application.Match(maxValue, searchRange, 0)
Set cell = Cells(maxRow,12)
cell.Select
End Sub
*********************************
Any help on this would greatly appreciated.
Thanks,
Chris
Hi All,
I am in a quandary over how to implement cell protection. It is common knowledge that for protection to work the cells must be locked and the worksheet protected. Well, when I try to implement cell protection using the following code:
Sub Worksheet_Activate
ActiveSheet.Protect UserInterfaceOnly:=True, password:="password"
End Sub
Either the target cells remain locked and a "Run-time error 1004' occurs and the macro stops, or the macro executes and leaves the target cells unlocked (and I'd like them to be locked when the macro has executed).
Can anyone shed light on this quandary? How can I acheive my objective of protecting the cell contents? It has been suggested that I set the sheets scroll area, however, this solution does not work for my situation.
Also, is there a way to lock cell after it has been unprotected?
Any help that I could get on this will be greatly appreciated!
Thanks,
Chris
Marcelo,
This protection problem is exasperating.
I have encountered the same protection problem you have described. I incorporated this statement in the worksheet module:
ActiveSheet.Protect UserInterfaceOnly:=True, Password:="password"
and I still receive the error message "Run-time error '1004': the cell or chart you are trying to change is protect and therefore read-only", each time I run my macros.
You indicated that you had placed your protection statement in the wrong place. Where??? I trying to determine if I have made a similar error.
Also, did you wind up using the suggestion supplied by Dave Hawley? If so did it work for you?
Thanks,
Chris
Thanks Hans,
I am going to try this immediately!!!
Thanks Again,
Chris
Edit:WillR[YES]
Hi all,
I trying to develop a macro to print my worksheets. Here's the problem, I never know how much data my end-users will incorporate into any one worksheet. In some instances, one worksheet can result in five pages or more of printout.
How do I acheive the following:
1.) set a page break so that if, for example, I have 400 rows or 4000 rows of data that each page print 40 rows data.
2.) print multiple row column headings on each page.
Iwould appreciate any help on the subject.
Thanks,
Chris:)
Thanks bnix,
I quess I was not going far enough into the object model. As it turns out there are only two arguments associated with the object 'character', start and length, respectively. To acheive my desired results, I managed to workaround this issue using an alpha character combined with the 'character' object.
Thanks Again,
Chris
Thanks bnix,
I tried the object browser first. It does not reveal the arguments associated with Characters, such as, Characters(arg1:=argument name, arg2:=argument name, and so on). That's what I'm looking
for.
Anyway, I appreciate your response, thanks for the info. I will try MSDN maybe I'll find it there.
Thanks,
Chris