Excel/HELLLLLP.....

Advertisement


Question
QUESTION: Hey Tom

all those issues worked out great.  I hope you remember me, anyway i completed everything, the but the following code i will call code 1, which you gave me works awesome as a macro, but when i put it into a activeX list box, telling the box where to find the range of clients, and then we see them, and put that code in there in order for the clients to see their records, and then click on a client to pull that record up.  It worked great even in there, until i clicked the other code i got from you that i will call code2 which was the save record code and it saved all that information, so basically code 2 saves the record and code 1 brings that record up.

so having said, having a list box on the same page without the client having to go to another page to see a list of theirs seemed better and is a great idea, just sure if that is possible, or that code 1 has to be run only by macro? not in vba.....or if there is another way, the listbox linked the choice to a specific cell, which is the record of that client, and that pulls up the record.  anyway let me post both those codes as a reminder, any aissistance would be greatly like always appreciated Tom!!!

Code 1 - Copies cells from Customer Index Page to the Customers Page.  When i click on this
it works great, pulls up the record and everything shows, but when i click on to save a record the code 2, it shows an error and takes me to a yellow hight to the following of this code 1....

.Range("H12:J12").Select ---- and its hightlighted in yellow


Private Sub ListBox1_Click()
Application.ScreenUpdating = False
Dim r1 As Range, ar As Range, r2 As Range
Dim s1 As String, s2 As String, s As String
s1 = "Z5:Z11,Z13:Z14,Z16:Z19,U5:W14,Q16:R16,R12:R14,R9:R10,"
s2 = "R8:S8,R5:R6,P9:P14,P6:P7,N16:O20,N13:N14,H12:J13"
s = s1 & s2
Set r1 = Worksheets("Customer Index").Range(s)
For Each ar In r1.Areas
Set r2 = Worksheets("Customer").Range(ar(1).Address)
ar.Copy
r2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
   
Next
With Worksheets("Customer")
.Range("K4,N12").Value = 0
.Activate
.Range("H12:J12").Select
End With
Application.ScreenUpdating = True
End Sub


Code 2 - Saves what is on the customers page as a record to the database which is where the Customer Index page gets its information.

Private Sub Label3_Click()
Application.ScreenUpdating = False
  Dim sh2 As Worksheet, sh1 As Worksheet, sh3 As Worksheet, sh4 As Worksheet, sh5 As Worksheet
    Dim bk1 As Workbook
    Dim r1 As Range, r2 As Range
    Dim NextRow As Long
   Cancel = True
    ' source workbook
    Set bk1 = Workbooks("ES 800 v11.00.xlsm")
    bk1.Activate
    Set sh1 = bk1.Worksheets("customers sold")
    Set r1 = sh1.Range("B2:N2")
    ' Target workbook
    Set sh2 = bk1.Worksheets("Sold Clients")
    Set r2 = sh2.Range("c4:o4")
    sh2.Unprotect
    NextRow = sh2.Range("c65536").End(xlUp).Row + 1
    If NextRow < 3 Then NextRow = 3
    r1.Copy
    sh2.Cells(NextRow, "C").PasteSpecial xlValues
    'r2.PasteSpecial xlValues  <== this line turned off; now a comment
    sh2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    
    
    Set sh1 = bk1.Worksheets("customers sold")
    Set r1 = sh1.Range("q2:ad2")
    ' Target workbook
    Set sh3 = bk1.Worksheets("Sold Inventory")
    Set r2 = sh2.Range("c5:p5")
    sh3.Unprotect
    NextRow = sh3.Range("c65536").End(xlUp).Row + 1
    If NextRow < 3 Then NextRow = 3
    r1.Copy
    sh3.Cells(NextRow, "C").PasteSpecial xlValues
    'r2.PasteSpecial xlValues  <== this line turned off; now a comment
    sh3.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    
    
    Set sh4 = bk1.Worksheets("Database Index")
    Set r1 = sh4.Range("c2:cv2")
    ' Target workbook
    Set sh4 = bk1.Worksheets("Database Index")
    Set r2 = sh4.Range("c6:cv6")
    sh4.Unprotect
    NextRow = sh4.Range("c65536").End(xlUp).Row + 1
    If NextRow < 3 Then NextRow = 3
    r1.Copy
    sh4.Cells(NextRow, "C").PasteSpecial xlValues
    'r2.PasteSpecial xlValues  <== this line turned off; now a comment
    sh4.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    

    Set sh5 = bk1.Worksheets("Inventory Index")
    Set r1 = sh5.Range("c2:ba2")
    ' Target workbook
    Set sh5 = bk1.Worksheets("Inventory Index")
    Set r2 = sh5.Range("c7:ba7")
    sh5.Unprotect
    NextRow = sh5.Range("c65536").End(xlUp).Row + 1
    If NextRow < 3 Then NextRow = 3
    r1.Copy
    sh5.Cells(NextRow, "C").PasteSpecial xlValues
    'r2.PasteSpecial xlValues  <== this line turned off; now a comment
    sh5.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Application.ScreenUpdating = True

End Sub

ANSWER: Anthony,


so remove that line

.Range("H12:J12").Select

you don't need it.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: well actually that was the first thing i did, and it went right back and this time highlighted the next line
which was

.Range("K4,N12").Value = 0

it tried a couple different things and nothing, but in order for me to use it i have to put a buttom on the page and asssign it was a macro, making it a two-step process.....

unless you know of another way to call up a record ....

ideas?

Tony

Answer
Anthony,

the code was tested from a third workbook before I sent it as I recall.  There is nothing wrong with the code - so you need to look at the error message and figure what the problem is which I suspect is environment related.  Perhaps the customer sheet is protected - if so, then you won't be able to change something on it.

As far as implimenting your code, I can't design your interface.  There are many different ways to do many different things, but I would have to have a deep understanding of what you are doing to tell you how you can interface with your user - having that level of knowledge conveyed in such a forum as this is not realistic.  

User interface programming requires understanding what you have, what you need to do and consider everything that the user could do - because they will always try to mess up what you are doing.  As I said, that requires much deeper understanding than what could be conveyed here.

I can answer specific technical questions for which I have knowledge and experience.

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.