Posts by sharkfoot

I have the combined two subs as follows:

Sub ClearCells()


    Dim strFirst As String, strMid As String, strLast As String
     
    ChDir "C:\Program Files\Total negotiating tool\TNT Locker\"
     '*****************
    Application.DisplayAlerts = False
     '*****************
    With Worksheets(1)
        strFirst = .Range("salesperson2")
        strMid = .Range("Guest")
        strLast = .Range("New_Vehicle")
    End With
     
    ThisWorkbook.SaveAs _
    "C:\Program Files\Total negotiating tool\TNT Locker\" & _
    " TNT - " & strFirst & " - " & strMid & " - " & strLast, xlWorkbookNormal
     '*****************
    Application.DisplayAlerts = True
     '*****************
    Range("salesperson2,salesmanager2,Guest,New_Vehicle,Stock_Number") = ""
    Range("Sticker_Price,Initial_Cost,Already_Discounted,Discount,Rebate,Down_Payment,Initial_Trade_Allowance,ACV,Trade_Payoff") = 0
    Range("Charge_For_First_Item,Cost_Of_First_Item,Charge_For_Second_Item,Cost_Of_Second_Item,Charge_For_Third_Item,Cost_Of_Third_Item") = 0
    Range("Reduce_Trade_Allowance_One,Reduce_Trade_Allowance_Two,Reduce_Trade_Allowance_Three,Reduce_Trade_Allowance_Four") = 0
    Range("Rate_One") = 0.0999
    Range("Term_One") = 48
    Range("Rate_Two") = 0.1099
    Range("Term_Two") = 54
    Range("Rate_Three") = 0.1199
    Range("Term_Three") = 60
    Range("First_Item_To_Add,Second_Item_To_Add,Third_Item_To_Add") = "Add Nothing"
    Range("Reconditioning_Item_One,Reconditioning_Item_Two,Reconditioning_Item_Three,Reconditioning_Item_Four") = "Enter Recon Item Here"
    Range("Tag_Selection") = "Car (New Tag)"
    Range("State_Selection") = "SC"
    Range("MRM,residual_percentage,Money_Factor,first_lease_term") = 0
    Range("residual_enhancement,Admin_Fee_Tweak") = 0
    Range("Points") = 0.03
    Range("First_Lease_Payment") = ""
    Range("adjusted_cap_cost") = "=SUM(C50-C55)"
    Range("First_Lease_Payment") = "=C67+C70"
End Sub

The first sub saves the file, the second sets the workbook up for a fresh deal to be worked. So what SHOULD happen is that when the user runs the macro, the existing workbook is save and THEN cleared. The way it is currently written, the workbook clears and THEN SAVES, so the new workbook is saved as a blank.

Can I place a delay in between the two subs so that it saves, then clears? If so, what code do I put where? Thank you in advance.

Re: Naming Sheets From Cell Contents

Quote from Dave Hawley

If you are here to learn then help us help you by sticking to the rules you agreed.

Just reviewed the tos and nope, nowhere does it say to be sure to accomodate Hawley's desire to see "thanks" in every post. Might want to ammend your tos. And if you review my previous posts, you'll see "thanks" all over the place. I'm VERY thankful for assistance. And sometimes I might even send a little private note of thanks to the helpful person as well. I'm never ever rude for the sake of being rude.

And I'm referring to the simple fact that, you especially, send little reprimand messages with regularity and don't specifically state what the user is doing wrong. All you say is "read the tos you agreed to when you signed up. Take note of ALL points." That response in answer to a biting question that I was trying to get help on. And I don't know what I did wrong. Reading it again...still no idea what was wrong. All I know is that the board monitor didn't approve of something. Who knows what. Maybe i didn't say thanks publicly in that thread either?

Like when someone posts a thread with a title that wasn't written correctly... Maybe you could say "instead of blah blah, next time try blah blah blah". THEN maybe you'd fix the problem you're seeing instead of just complaining. Some of us aren't too bright in the first place

Thanks Mr. Hawley!

Re: Naming Sheets From Cell Contents

Quote from Dave Hawley

Sharkfoot, why not state you whole needs 1st up rather than only giving some of your needs with each post?

BTW, rather than only throwing bits of your whole problem at StephenR I think the word "thanks" would go a long way.

Since I had already told him thanks I figured I would be being redundant. And the board monitors around here seem to be more interested in quipping (like your post) than actually helping new people learn, so had I said thanks again I'd likely get a nasty private message. And lord knows I'm here to learn, not be reprimanded or criticized by folks that know more than I do.

But thanks for the tips, buddy! I'll try to do better for ya!

Re: Naming Sheets From Cell Contents

Quote from StephenR

Right-click on the VBA Project or Microsoft Excel Objects heading in the Project Explorer window, Insert Module and paste the code into the module. That should work.

Ok, I got that to work but I added a couple of sheets to the workbook and need them to be excluded from the renaming process. How do I do that?

Re: Naming Sheets From Cell Contents

Quote from StephenR

Here's one way. You have to look at the worksheet order in the VBE to confirm the order as it is not necessarily the order of the sheets in your workbook.

Code

Sub x()
Dim r, w As Integer
Dim wks As Worksheets


r = 1


For w = 2 To 7
    r = r + 1
    Worksheets(w).Name = Worksheets("Variables").Cells(r, 1)
Next w


End Sub

Display More

Works with a button click. How do I make it automatically run when the sheets is selected?

I have a workbook with a sheet titled Variables. There are 6 additional sheets in the workbook and each sheets needs to be named with the cell contents of A2:A7 in the Variables sheet. I have attached the workbook and you can look at the names in the sheets tabs to see what I'm trying to do here. Thanks in advance for any help.

The workbook is too large to attach but can be viewed here:
http://www.shuffleupanddeal.org/name_sheets.xls
or
http://www.shuffleupanddeal.org/name_sheets.zip

I have the following macro in a workbook:

Sub SplitDeal()


    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:K2"), Type:=xlFillDefault
    Range("A2:K2").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=Agency!R[1]C/2"
    Range("A2").Select
    Selection.Copy
    Range("H2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("H2:K2"), Type:=xlFillDefault
    Range("H2:K2").Select
    Range("A2").Select
End Sub

The problem is that I need the macro to select the cell contents of A2 and then divide whatever value is in the cell by 2. In other words, if A2 has =Agency!A3, the macro would make i =Agency!A3/2 or if A2 contains =Agency!A17 then the macro would change the contents of A2 to =Agency!A17/2. So NO MATTER WHAT IS IN A2, the macro would still replace the [contents of A2] with [the contents of A2/2].

In case it helps make sense of this likely very easy problem I have attached a workbook. Thanks for any ideas.

As the macro is currently written, A2 would ALWAYS have to be

Re: Lookup Function Returning Incorrect Data

Quote from norie

Well I'm not actually 100% sure what you want to do but I used this formula.

Code

=VLOOKUP(Tag_Selection,Sheet1!$A$5:$B$18,2,0)

Works perfectly. Not sure why my lookup formula didn't work but the vlookup does the trick. I wish I understood better as to why though. At any rate, thank you!

Re: Lookup Function Returning Incorrect Data

Quote from norie

Why not use VLOOKUP?

what would I do differently?

I have a simple lookup function that keeps returning incorrect values. The crazy thing is that if I change the list options from text to numbers it works correctly.

I have attached a copy of the workbook. Just change the data in B3 (drop-down menu) and you'll see what I mean. If you cange A5 through A18 to numbers instead of text then it returns the correct value. Any ideas?

Re: Compile Error In Hidden Module

Quote from gmccreedy

can you ZIP it?

It was still much too large... but I figured it out with the help of Macropheliac. It seems my workbook has a checkbox by MISSING: Adobe Acrobat 7.0 Browser Control Type Library 1.0

A couple of questions.... (1) why is that checked in the first place (2) SHOULD it be checked or is it normally required and (3) where can I download the Adobe Acrobat 7.0 Browser Control Type Library 1.0?

Re: Compile Error In Hidden Module

Quote from Macropheliac

Well, I don't know. Can you post an attachment? I'll give it a look and see what I can come up with.

I took a lot of sheets out of this workbook to keep the info private but I left the sheet(s) that show the problem.

I tried to upload the file but it is too large for this forum. Shall I email it to you? I need an address to emnail it of course

Re: Compile Error In Hidden Module

Quote from gmccreedy

It also sounds like your have something wrong in the coding of the userform.

If there is something wrong with the coding if it works on most pcs but not on others? I'm ignorant to this but learning! Thanks!

Re: Compile Error In Hidden Module

Quote from Macropheliac

It might. Try adding:

Code

.Text

after UserNameTextbox.

iFoundPass = .Find(What:=UserNameTextBox.Text, After:=.Cells(1, 1), LookIn:=xlValues,

I changed the code above to reflect the .Text you suggested but it doesn't seem to change things. Should I put .Text after every instance of UserNameTextBox in the sub? Or should I try something different now?

Re: Compile Error In Hidden Module

Quote from Macropheliac

The line:

Code

iFoundPass = .Find(What:=UserNameTextBox, After:=.Cells(1, 1), LookIn:=xlValues,

What is UserNameTextbox?

OK, userform contains two text boxes, one for the username and one for the password. The one that you enter your username into is called "UserNameTextBox". PasswordTextBox is the name of the box the user inputs the password into. Does that help us?

Re: Compile Error In Hidden Module

Quote from Macropheliac

The line:

Code

iFoundPass = .Find(What:=UserNameTextBox, After:=.Cells(1, 1), LookIn:=xlValues,

What is UserNameTextbox?

Well, UserNamesis the name of the list that it is checking against and that is defined as: "=Config!A$1:A10"

But isn't that line referring to the text box in the userform? I don't know I'm just guessing. But I have nothing defined as UserNameTextbox.

Does this line up with the fact that I CAN log in on all of these other PCs but not on just two of them?

Thanks again!

Re: Compile Error In Hidden Module

Quote from gmccreedy

Do you have external addins that need to be installed? I would also suggest running the update manager for Office. I had problems similar to this on some PC's in my office and all I really needed were a few updates.

As far as the "data" highlighted Burgundy...is that the "step through" break point? Is there a Burgundy dot in the left hand margin?

It also sounds like your have something wrong in the coding of the userform.

I've never installed an add-in, no. And I've updated both PCs with all of the latest and greatest updates for windows, excel and microsoft office. Still no dice.

There is a yellow hightlight to the left of the yellow highlighted sub name, but the burgundy highlighted word "Date" has no red dot by it.

Did I answer the question correctly?

Re: Compile Error In Hidden Module

Quote from Macropheliac

What is usernametextbox? It has to be declared.

Well, the usernames are in a list that is defined as "UserNames". Is that what you mean? I have the usernames defined as A1:A10 on a sheet called "Config".

Re: Compile Error In Hidden Module

Quote from Macropheliac

Click Tools + Macro + Visual Basic Editor, right click the approppriate project to the left, Type the password.

OK. Unlocked the code and here is the new error:

"Compile error:

Can't find project or library"

When I "debug" it give me a yellow highlight on the following sub:

Private Sub OKButt_Click()
Dim iFoundPass As Integer
On Error Resume Next
With Sheets("Config").Range("UserNames")


   iFoundPass = .Find(What:=UserNameTextBox, After:=.Cells(1, 1), LookIn:=xlValues, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Row
End With
On Error GoTo 0


If iFoundPass = 0 Then
   SomethingWrong
      Exit Sub
End If


If Sheets("Config").Cells(iFoundPass, 2) <> PasswordTextBox Then
   SomethingWrong
      Exit Sub
      ElseIf Sheets("Config").Cells(iFoundPass, 3) < Date Then
      Expired
      Exit Sub
End If


Sheets("Config").Range("LoggedInAs") = UserNameTextBox
Unload Me




End Sub

Only the first line is highlighted in yellow. Then, if you go down a bit in the script, the word "Date" is highlighted in the normal highlight color (burgundy?). Right after the 3) < section.

What's curious to me is that it works on most all of the pcs here at my office, but two of them are giving me this error. The rest do not. ALL of the pcs have the same operating system and the same version of Excel.

So what's going on here? Thanks again!

Re: Compile Error In Hidden Module

Quote from Macropheliac

Hello Sharkfoot! (Great screen name BTW.)

Try unlocking the VB and see which line is erroring.

Thanks for the compliment!

Ok, unlocking the vb.... how do I do that? I managed to lock it just fine

Thanks again!