Posts by Bubbis Thedog

Re: formula cell hide and unhide

Here's an option (sorry, I'm not good enough at VBA to write it):

User Option 1: If user chooses to input the start time and end time, then the cell that contains the formula that calculates total hours could be locked upon entering the values into those two cells.

User Option 2: If user chooses to enter the total hours, then, upon clicking the Total Hrs. cell, a message box will pop-up telling user something like, "If you enter a value in this cell, you are opting not to enter a start time and end time. Do you want to proceed?" If user chooses Yes, then user is allowed to enter a value into the cell, and the start time and end time cells would be locked automatically. This would prevent user from entering conflicting start and end times whose difference do not equal the total hours amount that user entered.

Hope that helps... If any of that is even feasible...

Bubbis Thedog

Re: NWO is changing to NOW

Tools --> Options --> Spelling tab --> AutoCorrect Options... --> uncheck Replace Text as you Type.

Hope that helps.

Bubbis Thedog

Re: textbox validate fixed numbers

Use the same code and format the cell to "00000000" using Custom formatting.

Bubbis Thedog

Re: Converting Hexadecimal numbers in VBA

Hello, Shark.

I'm probably explaining the obvious, but I have a DEC2HEX() function. It was part of the Analysis Toolpak Add-In that you should have.

Bubbis Thedog

Re: formula cell hide and unhide

Maybe the attached worksheet will help --if I've understood you correctly.

Hope it helps.

Bubbis Thedog

Re: Getting AS/400 Data to Link Real-Time...

Thanks so much for the info, you two! I'll check out those resources, Brandtrock. Have a great weekend! I know I will! :drink:

Bubbis Thedog

Hello, all. Forgive me for my ignorance, but I'll try to explain this as best as possible. We run AS/400, and the data is stored on a DB2 server. Where can I find information that will lead me through the steps of getting the data from of the server into pre-formatted Excel worksheets? I'd like changes on the server to reflect 'real-time' in Excel if possible.

I need a book, or something of the like, that will speak to me in plain English -if such a tool even exists. Do I really need a book to learn how to do this? I'm finding it difficult using the Internet to get even the most basic information about this. Any information would be very helpful!

Thanks,

Bubbis Thedog

Re: format returns to date

I had it right, just enter "mm/yyyy" instead of "dd/yyyy." DUH. Sorry, I was busy. Hope that helps.

Bubbis Thedog

Re: format returns to date

Sorry, Blaine; I jumped the gun. I only tried what I said to do with "04/04" and it returned the correct format. With other dates it does not work. I'll figure something out here in a minute --unless someone beats me to it.

Bubbis Thedog

Re: format returns to date

You need to type it in right under where it says "Type:". Give 'er another shot, Blaine.

Bubbis Thedog

Re: format returns to date

Click on the column header A --> right click --> Format Cells... --> Custom --> Type DD-YYYY in the little white box. That should do it.

Bubbis Thedog

Re: Copy Paste data from one file to 3 files

No apology necessary, Batman. Dansoo had posted the query on the wrong forum, which is why he/she was apparently not getting a response. That message was moved to the Excel/VBA forum, so I was hoping that someone with more VBA experience than I --like yourself-- would answer it on there. Have a good one, friend.

Bubbis Thedog

Re: Copy Paste data from one file to 3 files

Dansoo,

I understand what you're wanting. I'll try to get you something here soon, friend. I'm very busy at work right now.

Bubbis

I'm right there with ya, booger! It's fun after they help you out with things. I learn a ton with every ridiculous question I ask. Thanks, you all!

Bubbis Thedog

Whoops! I had fixed the semicolon, changing it to an apostrophe, but just realized that I was missing those parentheses. It works great now, Derk. Thanks a lot for taking the time to help me, man.

Bubbis Thedog

Excellent. Thanks, you two, for the help. I now see what I was doing wrong. But, Derk, I actually would like the formula to be invisible in the cell; that is, I'd like it to be functionable. Do I have to write in the VBA that I want the string (the formula) written in the formula bar or something? I tried to simply delete the tick mark in quotes and the ampersand and I get an error. Thanks again for the help.

Bubbis Thedog

Hello, friends.

Below is some code that I wrote in order to insert cell formulas into my worksheet:

Sub InsertFormulas1()


Dim FormLoc As Integer, CommonForm As String
CommonForm = "=IF($B$4<K$10,""," & _
              "INDEX('2004 Ratios Referral Sheet'!$A$1:$DD$3000," & _
              "MATCH($A" & FormLoc & _
              ",'2004 Ratios Referral Sheet'!$A:$A)," & _
              "MATCH(K$9,'2004 Ratios Referral Sheet;!$A$1:$DD$1)"


  For FormLoc = 13 To 138 Step 5
    Range("K" & FormLoc).Select
    ActiveCell.Value = CommonForm
  Next FormLoc


End Sub

Well, it's not working. When I debug, it's telling me that the problem is with the "ActiveCell" line, but I don't know how to fix it. I know that this is very simple, but I'm a newbie. Thanks for any help that you can provide.

Bubbis Thedog

Blimey, sorry for being a nuisance. hahahahah :facepull: Yes, that code that you wrote for me works perfectly, Will. Thanks for taking the time to help me!

Bubbis Thedog

Will, I hope I haven't wasted your time, friend. A132, A273, and A414 were only examples to show you the increment involved. There are actually thousands of rows in the real worksheet and I was looking for a way to not have to input the position of each cell in the code (Range("A132, A272, ..., An")). I've found a way to accomplish what I wanted to do by copying and pasting; now I'm wondering if there is a better (maybe even faster compile-time-wise) way to do this. Thanks so much for your assistance, Will!

Here's the code that I have:

Sub PartII()
Dim InCell As Integer
Application.ScreenUpdating = False


Range("FF1").Select
ActiveCell.Value = InputBox("Enter the new state abbreviation", "New State Entry")
    For InCell = 132 To 414 Step 141
    Range("FF1").Select
    Selection.Copy
    Range("A" & InCell).Select
    ActiveSheet.Paste
Next InCell
Range("FF1").Select
Selection.Delete


Application.ScreenUpdating = True
End Sub

Bubbis Thedog

Thanks, Will, for the help. I need the user to input the state only once into the InputBox, however, and then that one state will reflect in all of the cells specified. I didn't make it clear that the same state needs to be input into each of the cells --as opposed to a different state in each one. Thanks again, Will.

Bubbis Thedog