The Cove Business Standards Discussion Forums More Free Files Forum Discussion Thread Post Attachments Listing Cove Discussion Forums Main Page
UL - Underwriters Laboratories - Health Sciences
Go Back   The Elsmar Cove Business Systems and Standards Discussion Forums > Common Quality Assurance Processes and Tools > Quality Tools, Improvement and Analysis > Quality Assurance and Compliance Software Tools / Solutions > Excel .xls Spreadsheet Templates and Tools
Forum Username

Elsmar Cove Forum Visitor Notice(s)

Wooden Line

Excel Macros - "Button" on a form that executes a macro


Elsmar XML RSS Feed
Elsmar Cove Forum RSS Feed

Monitor the Elsmar Forum
Sponsor Links




Courtesy Quick Links


Links that Cove visitors will find useful in your quest for knowledge:

International Standards Bodies - World Wide Standards Bodies

ASQ - American Society for Quality

International Standards Organization - ISO Standards and Information

Howard's
International Quality Services


Marcelo Antunes'
SQR Consulting, and
Medical Devices Expert Forum


Bob Doering
Bob Doering's Blogs and,
Correct SPC - Precision Machining


Ajit Basrur
Claritas Consulting, LLC


NIST's Engineering Statistics Handbook

IRCA - International Register of Certified Auditors

SAE - Society of Automotive Engineers

Quality Digest

IEST - Institute of Environmental Sciences and Technology


Some Related Topic Tags (Not all threads are Tagged)
microsoft excel .xls .xlsx spreadsheet, spreadsheets, macros (in spreadsheets and databases)
Reply
 
Thread Tools Search this Thread Rate Thread Content Display Modes
  Post Number #1  
Old 28th September 2011, 12:02 PM
JaimeB

 
 
Total Posts: 22
Please Help! Excel Macros - "Button" on a form that executes a macro

Hi!

We are in the process of building a CA/PA form and a log to track data from the form.

My vision is to have a "button" on the form that executes a macro that will open up the log and update it with the data we want to track from the form.

I've created a macro that works the first time, but the second time I end up with either a run time error, or the data gets placed in the log but not in the right destination cells.

I know enough about Excel to be dangerous, but not enough to accomplish what I want to. Does anyone have any suggestions?

Sponsored Links
  Post Number #2  
Old 28th September 2011, 12:06 PM
Hodgepodge's Avatar
Hodgepodge

 
 
Total Posts: 281
Re: Need help with a macro.

Please post the macro or, preferably, the work book so we can see what you are trying to do. Also, please highlight the code where the runtime error occurs.
Thanks to Hodgepodge for your informative Post and/or Attachment!
Sponsored Links

  Post Number #3  
Old 28th September 2011, 12:07 PM
somerqc's Avatar
somerqc

 
 
Total Posts: 436
Re: Need help with a macro.

Without seeing the macro it is difficult to tell you exactly; however, it sounds like you have either a loop that isn't working properly or the lack of a loop creating an issue.

Creating a log through this scenario requires that you tell Excel to drop down a row to enter the new information. If this is missing, you may get errors or if the the process to do this is incorrect you will get errors.

Another source may be the programming "behind" the button. This can be tricky at times depending on what you want it to do.
Thanks to somerqc for your informative Post and/or Attachment!
  Post Number #4  
Old 28th September 2011, 12:22 PM
JaimeB

 
 
Total Posts: 22
Re: Need help with a macro.

Here is the form and log. Right now I only have the macro pulling data from three cells in the form so as to not spend an exhorbitant amount of time rebuilding the macro each time it doesn't work. Once I get it working, I want to include data from other sections of the form as well.

Here is the macro, emphasis on the line where the error occurs:

Sub log()
'
' log Macro
'
'
Application.WindowState = xlMinimized
Workbooks.Open Filename:="\\rdata\home\jbithell\Desktop\NCR Log.xlsx"
ActiveCell.Offset(-11, -9).Range("A1").Select
ActiveCell.FormulaR1C1 = "='[XX - NC Form.xlsx]NCR'!R1C7"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='[XX - NC Form.xlsx]NCR'!R2C7"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='[XX - NC Form.xlsx]NCR'!R3C7"
ActiveCell.Offset(1, -2).Range("A1").Select
End Sub


Please let me know if I can provide anything else to help you help me!

Edit - I just noticed this, I renamed the form and log before attaching it, which is not reflected in the macro. Sorry about that.
Attached Files: 1. Scan for viruses before using, 2. Please report any 'bad' files by Reporting this post, 3. Use at your Own Risk.
File Type: xlsm 01 - NCR Form.xlsm (24.1 KB, 1700 views)
File Type: xlsm 02 - NCR Log.xlsm (8.2 KB, 1203 views)
  Post Number #5  
Old 28th September 2011, 12:54 PM
JaimeB

 
 
Total Posts: 22
Re: Need help with a macro.

So I kept searching, and found a bit of code that seems to have solved my problem!

http://www.computing.net/answers/off...row/10409.html

Thank you for your help.
  Post Number #6  
Old 28th September 2011, 01:00 PM
Hodgepodge's Avatar
Hodgepodge

 
 
Total Posts: 281
Re: Need help with a macro.

What do you want to do when the workbook opens? Select Range "A1"? Or select the active cell (who knows where that will be) offset? Don't use the active cell offset with Range.

Let me know what you are trying to do. If you are trying to use formulas to fill in the blanks, there will always be issues. Also, is there a need for the two workbooks. Both could be in the same workbook.
  Post Number #7  
Old 28th September 2011, 04:05 PM
dkusleika

 
 
Total Posts: 54
Re: Excel Macros - "Button" on a form that executes a macro

Let's see if you can become even more dangerous! Here's some code to update the log from the button on the form. Basically, it checks to see if the NCR has already been recorded. If it has, it asks whether you want to overwrite or not. If it hasn't, it finds the next blank row and records the NCR.

Code:
Sub log()
    
    Dim shForm As Worksheet
    Dim shLog As Worksheet
    Dim rFound As Range
    Dim lResp As Long
    Dim sMsg As String
    
    Const sLOGFILE As String = "\\rdata\home\jbithell\Desktop\NCR Log.xlsx"
    
    Set shForm = ActiveSheet 'the sheet with the button on it
    Set shLog = Workbooks.Open(sLOGFILE).Sheets(1)
    
    'See if the NCR exists in the log
    'assumes NCR# is unique and in column A
    Set rFound = shLog.Columns(1).Find(shForm.Range("G1").Value, , xlValues, xlWhole)
    
    'if the ncr exists
    If Not rFound Is Nothing Then
        'make sure they know they're overwriting data
        lResp = MsgBox("NCR Already exists.  Do you want to overwrite?", vbYesNo)
        
        'if ok to overwrite
        If lResp = vbYes Then
            'write the values to the log
            rFound.Value = shForm.Range("G1").Value
            rFound.Offset(0, 1).Value = shForm.Range("G2").Value
            rFound.Offset(0, 2).Value = shForm.Range("G3").Value
            
            'save and close the log
            shLog.Parent.Save
            shLog.Parent.Close False
            sMsg = "NCR data updated."
        Else
            sMsg = "NCR data not updated."
        End If
    'if the ncr is new
    Else
        'find the next blank row
        Set rFound = shLog.Cells(shLog.Rows.Count, 1).End(xlUp).Offset(1, 0)
        'write the values to teh log
        rFound.Value = shForm.Range("G1").Value
        rFound.Offset(0, 1).Value = shForm.Range("G2").Value
        rFound.Offset(0, 2).Value = shForm.Range("G3").Value
        
        'save and close the log
        shLog.Parent.Save
        shLog.Parent.Close False
        sMsg = "New NCR logged."
    End If
    
    MsgBox sMsg
    
End Sub
One thing that's substantially different in the above code is that it writes the values to the log rather than inserting formulas. What I don't particularly like about using formulas in logs is that the source file may get accidentally moved or deleted. If that happens, the data in the log goes away.

I tried to include enough comments so you can follow along, but if you have any questions about the code, feel free to ask.

Last edited by dkusleika; 28th September 2011 at 04:08 PM. Reason: fix code formatting
Thank You to dkusleika for your informative Post and/or Attachment!
  Post Number #8  
Old 28th September 2011, 04:22 PM
Hodgepodge's Avatar
Hodgepodge

 
 
Total Posts: 281
Re: Excel Macros - "Button" on a form that executes a macro

Here's an option that uses only one workbook. Even if you don't use just one workbook, you can see the code and how it works. I also included a button for searching NCR #s so you can fill in the form with the data in the Log Book.
Attached Files: 1. Scan for viruses before using, 2. Please report any 'bad' files by Reporting this post, 3. Use at your Own Risk.
File Type: xlsm NCR Form.xlsm (38.8 KB, 388 views)
Thanks to Hodgepodge for your informative Post and/or Attachment!
Reply

Lower Navigation Bar
Go Back   The Elsmar Cove Business Systems and Standards Discussion Forums > Common Quality Assurance Processes and Tools > Quality Tools, Improvement and Analysis > Quality Assurance and Compliance Software Tools / Solutions > Excel .xls Spreadsheet Templates and Tools

Bookmarks



Visitors Currently Viewing this Thread: 1 (0 Registered Visitors (Members) and 1 Unregistered Guest Visitors)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Forum Search
Display Modes Rate Thread Content
Rate Thread Content:

Forum Posting Settings
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Discussion Threads
Discussion Thread Title Thread Starter Forum Replies Last Post or Poll Vote
Have you heard about "Ample Amps" - "Miraculous Motors" - "Maxeff" ? (or Wanlass) Popeos Coffee Break and Water Cooler Discussions 10 19th December 2012 02:58 AM
How do I "Drop Down List" that is a "pick all that apply" in Excel dtzcollins Excel .xls Spreadsheet Templates and Tools 6 26th August 2010 09:15 AM
Process Mapping - for "Audits" - Detailed process map + macro map + FMEA abhipatel Process Maps, Process Mapping and Turtle Diagrams 10 27th July 2009 10:48 PM



The time now is 07:31 PM. All times are GMT -4.
Your time zone can be changed in your UserCP --> Options.


 
 


NOTE: This forum uses "Cookies" - A Peachfarm Internet Property