Posts by Karebac

Re: Self Destruct Workbook After Not Logging In Within 30 Days

I did some searching on the topic of compiling Excel spreadsheets. I came across this

KDCALC

I searched this ozgrid.com forum on KDCALC, but found no mention of it.

It does not seem to be able to compile VBA code, although it mentions something about porting VBA to vb.net. It does sound like a way to develop a spreadsheet that one might completely control.

Re: Open Dialog

Reafidy, I assume you are saying that the dialogue will allow navigation to any drive or folder, and many other openfile dialogues do. I have not tried this one myself. Though, perhaps someone would want the dialogue to open to a specific folder, if the user were not very familiar with such navigation.

P.S.
Yes indeed, I did a little experiment, and wrote this macro code

Sub openmyfile()
FileToOpen = Application.GetOpenFilename()
MsgBox FileToOpen
End Sub

And sure enough, it allows me to navigate to any folder, click on a file, and then shows me the file path in the msgbox.

I have only been studying this for one week, so sorry if I seem rather simple-minded.

Re: Open Dialog

I did a search on this site just now on getopenfilename, and came up with a number of threads

This thread looks promising

Re: Self Destruct Workbook After Not Logging In Within 30 Days

Here are some ideas that come to my mind as I read this intriguing post:

IF there is some way that the user can only access your spreadsheet across the Internet, then you would have control of their access to the application.

Is there is some way that all the essential formulas might be updated remotely, each time the spreadsheet is loaded, and then delete them before the data is saved?

Is it possible to write some active-x add-in component which you could control in some fashion?

Or perhaps your application could require a password based upon month and year, and each month they would have to contact you for the new password?

I used to see applications with "key" devices that plugged into the parallel port. You could not run the application unless you purchased a key, and installed it on the machine.

Or, is there some way to encrypt their data, such that you can control the manner in which it is unencrypted. Or is it possible to design an excel spreadsheet which remotely loads the data across the Internet (from a site that you control, of course) and in that fashion, you would always hold their data captive.

Obviously, legal contractual agreements are a different way to enforce the use of your software creations.

I once purchased a license for a World Clock application which would show the time and date of as many countries as you please to add. The application would constantly communicate with their server, which could detect if the application was running on two machines. The moment their server detected an unauthorized installation, it would deactivate the program on BOTH machines, and require you to purchase an additional license. I dont know if Excel applications can be configured in such a fashion.

Suppose there was some programming language, that you could compile and control, which would CONTAIN within it, encrypted, your entire Excel spreadsheet application. Each time the user wanted to use your application, they would launch THAT program, which would construct the Excel spreadsheet, and populate the data (perhaps encrypted in some sql application). When the user exits your application, it backs up all the data into that external encrypted database, and then deletes or destroys itself. If all this is possible, then you would have control of the program which reconstitutes the spreadsheet. And I suppose, if you could develop such a "meta-application" which could automatically encapsulate any spreadsheet app, well then you would have something marketable to others who desire such security or control.

Suppose your client user were to sign a contractual agreement with you to lease a computer from you which runs your Excel spreadsheet. That computer would be heavily protected with encryption and passwords in some fashion, and would only run when connected to the Internet, and would give you total control over it's use. Of course, in such a circumstance, a user might be justifiably concerned that at some future time, you would be holding their data captive. They might want some clause that allows them a one-time export of their data to machine readible format before either party dissolves the relationship.

Hardware protection devices are called "dongles" (among, presumably, many other things.
Here is a "dongle overview" by a company which is probably fairly high tech. The dongle plugs into a USB port (and they have parallel port dongles). You lace your application with periodic calls to the dongle. Now, what I am trying to find out is whether they have a dongle which can be contacted and configured remotely, across the Internet. Their literature does make one noteworthy statement: Software protection should make it more expensive to pirate software than to purchase it.

Here is a dongle which mentions remote internet configuration. Here is an illustration of how that remote dongle update works. Now, I only have to find out how to make calls to the dongle via VBA (and of course, there must be some way to protect the VBA so that some rascal does not simply remove the calls).

Re: The Philosophy Of Option Buttons

Thanks, Shg! If rows.count is the bottom left-hand corner, then everything becomes crystal clear for me. Now you have me wondering what on earth rows.count denotes in Excel 2007, and why it should be different from other versions.

Re: The Philosophy Of Option Buttons

Question for RoyUK about your lines of code:

' find first cell available for input
'use End(xlUp) to avoid any empty rows in the table
Set rNextCl = wsData.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

I see that it works, but I am trying to understand how it is working.

By contrast, I can clearly understand a command like

Range("A65536").End(xlUp).Offset(1).Select

which goes to the bottomleft of the sheet, pushes end up arrow to reach the first non blank cell, and then drops down one row, to be at the next blank row.

I am guessing that wsData.Cells(rows.count,1) is positioning us to the bottom-most row containing data. But if this is true, then what is the effect or purpose of End(x1UP)?

I.e. consider a small spreadsheet
A1=1 B1=11
A2=2 B2=22

which is a small square of data, whose row.count is presumably TWO. So, I would assume that your code positions us to cell A2 even without an END(x1UP).

Please, can you possibly help my beginner's mind to understand the logic underlying this line of code, with some further explanation or simple illustration?

Re: Go Liverpool!!!

Try as I might, I have never been able to cultivate a great interest in sports. But I have long been fascinated with language.

The title of this post reminded me that a native of Liverpool is called a
Liverpudlian.

As a child, I picked up the expression "a bit of a sticky wicket", but for years, never realized what a sticky wicket really is. In my mind, I imagined some type of bat made sticky by candy, and therefore difficult to hold. One day, I asked a native of a former British colony, where cricket is quite popular, and she explained to me what is best explained and illustrated
here.

I am also reminded of the oft heard American expression "stymied". It is only recently that I learned that the term "stymied" comes from golf, but is a situation that no longer arises in golf, since the rules were modified many years ago, to allow your opponent's ball to be moved, so you may have a clear shot. Again, I defer to wikipedia as the best source of explanation.

Hence, a stymied Liverpudlian is indeed a sticky wicket.

I realize that the game in question here is neither golf nor cricket, but, most likely,
soccer.

Since my post consists of some curious sports trivia, I shall conclude by mentioning one of my favorites: namely, that it took some years for people to realize that a basketball hoop should have a hole for the ball to pass through! We have discovered fire, invented the wheel, and even unleashed the energy of the atom, but for all this, we are rather dense, and slow about things.

Re: The Philosophy Of Option Buttons

I wanted to thank Dave Hawley and RoyUK this morning for taking the time to show me these useful new things, but I hesitated, thinking that perhaps I might be breaking a rule, or wasting bandwidth. But then, I decided to study the "Rules We Are Bound By" and discovered that per rule 15 Please take the time to thank those who took their time to help you.. I am both permitted and expected to express thanks.

Dave Hawley really opened my eyes to the nature of optionbuttons and their relation to frames by pointing out that :

OptionButtons also have a GroupName Property that can be set to "group" OptionButtons without the use of a Frame Control.

And an extra special thanks to RoyUK for taking the time to rewrite all the code, which I am printing out as a page for my notebook, so that I may study it during the week.

Especially helpful to me is the .Offset(0, C) technique, and also the insight into combo box initialization as

cboCourse.List = Array("Access", "Excel", "PowerPoint", "Word", "FrontPage")
        'make sure  combos are displayinf empty
        cboDepartment.ListIndex = -1
        cboCourse.ListIndex = -1

As I study your code, other questions come to mind, but I realize from the rules of the forum that I should try my best to answer them myself, first, and, after I have exhausted other resources, I should formulate each question concisely as a single new post, and not return to this thread.

Re: Counting To Column G

I have always suspected that having more girls who are less sweaty is a sure-fire formula for happiness, but your experience now proves this beyond a shadow of a doubt!

Re: The Philosophy Of Option Buttons

Quote

OptionButtons also have a GroupName Property that can be set to "group" OptinButtons without the use of a Frame Control

Thanks! The above is quite helpful to my beginner's education.

Sorry that I wrote so much. I am getting the message. This is a very valuable forum for me, so I shall pare down each post to the bare minimum "legitimate question", and avoid rocking the boat.

Re: Detecting Scanner Vs Manual Input

I tend to post in chat, because, my mind works the way it works, and I am terrified of posting the wrong thing in the wrong place, and ususally, I am ultimately driven away from forums, because the nature of my thinking process is radically different from others (albeit, it is the only one I have), but I shall be happy to attempt to comply with your request. But you see, if this is geniunely a "have a chat" section, then I should literally feel free to express myself without fear of censure, as long as I am cordial, and perhaps, expressing something related to spreadsheets and programming. And, as you can see, I do attempt to post legtimate questions in the Excel Help area, although, as I follow my thoughts to their natural conclusion, even those posts might appear "out of the ordinary", although to me, they are perfectly logical.

But, then, the power of corporate endeavor, is our diversity, and not our conformity, or uniformity, and the undue enforcement of conformity can sometimes have the adverse effect of dampening creativity, or brainstorming.

I certainly do not see myself as an equal to Socrates, but I sense that there is a cup of hemlock awaiting every Socratic type, somewhere down the road.

Sorry, if I am such a square peg in a round hole (or, am I a rounded peg, in a square hole?)

Tonight, as I liberally sipped burgundy, I worked though this wonderful beginner's exercise in creating a VBA form.

I noticed that it has a frame, and within the frame are three option buttons. I became quite curious as to the relationship between option buttons and the frame. With no coding on my part, the little buttons seem to mystically sense their presence inside of a frame, and dutifully acquiesce to whichever new option is clicked, so only one option at a time may be true. I decided to experiment. I created one lone option button outside of the sacred frame. I was able to click it on, and, lo and behold, I could not click it off! When I cut and pasted it inside the frame, with its brethern, it automatically became obedient. Well, I pasted it again outside the frame, and experimented with how I might turn it off. Now, my perverse nature began to sense that such behavior is the venue of a checkbox and not an optionbox. But, like Don Quixote, jousting at windmills, I was determined to have my way. The only methods I found to turn a lone ranger option box off, once checked, was to use the keydown option, and set it to false, which is obscure and tacky to say the least, or to place a label next to it, entitled, "turn the blasted thing off" and, in its click event, set the option button to false.

What I deduce from all of this is that the proper function of the option button lies within the boudaries of a frame, with one or more brethen option buttions. Am I correct in my surmize? And otherwise, one should be using a check box, which may be toggled on or off to the heart's content?

Hmmm. I have not thought to test the function of checkboxes residing within a frame (until just now!).

Experimentation reveals that if I add several check boxes to the frame containing the option buttons, there is no apparent affect. The option buttions still obey the frame rules for radio button logic, and the check boxes may be toggled on and off at will.

So, I ask myself the next obvious philisophical question, namely "what would be the use for a lone option button that, once turned on, could NEVER be turned off.

Well, in our society, I can think of two obvious applications.

One: "Have you ever been a member of the Communist Party"

Two: "Have you ever been attracted to the same sex."

Now, in our society, once there is the slightest hint of either option, it can NEVER be turned off. Even if you need better reading glasses, and you mistook "same sex" for "SOME sex", no matter, you checked the option and now you are branded.

And if you want to cancel my form, think again, because my VBA code updates with ever change event.

This option button philosophy suggests to me an interesting social commentary. I was once asked to write a literary analysis of the movie, "Brokeback Mountain" and the short storie by Annie Proulx. I asked various people, randomly, if they had seen the movie. I asked one African American male, and he recoiled from me and say "Ewww, you would watch that movie." In his mind, I had checked that option box, and there was no turning back. I gave him the following explanation. "Imagine an athlete who was notorious for seducing over 1000 women, but suddenly the tabloids revealed that as a teenager, he had one same-sex involvement. What would you say of him?" Of course the answer was that he was one of those "brokeback mountain" folks. So, then I asked him, "well, suppose someone was notorious for 1000 same sex involvements, but the tabloids revealed that, as a teenager, he had a relationship, with a girl?" Well, we know the reaction, same thing "a brokeback mountain fellow."

So, I progressed to a similar analogy. I said, "You see me as Caucasian, but, suppose you learned that my great great great great grandparent was African? Would you then see me as African-American? But, if your great great great great grandparent had been Caucasian, would you consider yourself Caucasian." He became quite flustered and said "that is different."

But, aha, is it really so different? Therefore, in our society, in our values, in our minds, in our stereotyping of others, we do require the data type of the lone optionbox, which can only be checked ON, and once on, can NEVER be checked off.

And, yes, this post is pertinent to excel vba forms, because the purpose of our forms and our datastructures is to accurately mirror our thinking, our values, and our society.

Re: Detecting Scanner Vs Manual Input

I did not realize that one may only edit a post for the first 60 minutes.

I thought of some more interesting barcode chat, and was going to edit my first post, but I shall add it to the end of this thread:

Here is a bit of a sticky wicket for you. Let us say that you wish to PRIVATE LABEL your product for someone, but it requires a UPC code. Well, if anyone is clever enough to use the UPC database on that code, then it will reveal the name of the Company which holds the license for that code. So, if you dont want people to realize that it is a private label, then the client company must pay and register with GS1-US, and produce their own UPC code.

Oh, and, when you do license to produce your own UPC codes, you receive some software from GS1-US which connects to their server. You can only create codes when you are online with the internet. Obviously, this is the only way that GS1-US can control all the UPC codes in North America, and guarantee uniqueness. You may export all your codes to an EXCEL SPREADSHEET (the mother of all exports), and use them offline, if you wish.

And, there are HIERARCHIES of codes. The UPC-A is for the package that passes across the scanner at the counter. Then there is a case/crate code, which is ITF-14 symbology, and denotes how many units are in the shipping case which arrives at the warehouse. Then there is a level for a CONTAINER, if you are sending a containerized shipment somewhere, but I have yet to make use of that level.

Re: Constantly Running Macro

By the way (I KNOW this is a dumb question), but do you LITERALLY have 123 files, or is this some pun on LOTUS 123? By the way QUATTRO was chosen by Borland as the name for its spreadsheet because it means FOUR, and would naturally be thought of as a successor to 1,2,3. Now I am remembering the ads I used to read for BOING Calc, and that inexpensive clone by Mosaic Softward, that I used for a spreadsheet, when I was too poor to purchase Lotus.

Re: Detecting Scanner Vs Manual Input

Thanks! That is EXACTLY what I wanted, but I could not think of the syntax, and I was in a hurry.

I was also curious about a command for instring, to test for the presence of a certain character string anywhere in a string, even though the scanner preamble would be at the start of the string.

Re: Constantly Running Macro

I am a total new-be to all this, but I have always been intrigued by such background events as you discribe here. Perhaps there is some potential in the Sync event in the Workbook module?

Private Sub Worksheet_Sync(ByVal SyncEventType As Office.MsoSyncEventType)


    If SyncEventType = msoSyncEventDownloadFailed Or _
            SyncEventType = msoSyncEventUploadFailed Then
            
        MsgBox "Document synchronization failed.  " & _
            "Please contact your administrator " & vbCrLf & _
            "or try again later."
    
    End If
    
End Sub

Years ago, I was a programmer on a Novell network of 50 DOS computers, using Revelation (PICK Basic for DOS). I made one workstation into a constantly running program which would search a certain file for new records, and whenever it found one, the record would be parameters for it to process some data submitted by one of the other workstations. So it was like a primitive job queue.

I wrote up my idea for a similar application, at utteraccess.com under the Karbac screen name, entitled "The Hamburger Marquee". You know how in those fast food joints, when you place an order at the drive-in, someone hits a button, and a marquee in the back tells the cook to flip another burger. Well, in a network environment, you could have a marquee on the boss's desktop, polling a file for a new record, such as "VIP CALL ON LINE 3", and when it gets a hit, the screen would flash, plus, all the calls would be logged and datestamped some place.

So perhaps, if you are on a network, you could have a dedicated task on one workstation, which continuously opens all those spreadsheets, and updates them with the desired codes.

Or better yet, when a user updates a spreadsheet, have the Excel VBA cut a record to some file, which is constantly polled by another workstation, and when that jobqueue application sees such a record, it will modify the spreadsheet accordingly.

I feel like chatting about my experiments teaching myself VBA.

I was thrilled to get my spreadsheet vba form to work with the scanner yesterday, and locate each inventory item in my spreadsheet. You can see that endeavor in my posted question in the Help section.

Today I became curious to know how I might design my form to detect whether field input is from a scanner, or manually input.

I did it with the following code

Private Sub TextBox1_Change()
 If TextBox1.Value = "^" Then
  ScannerInput1.Value = True
 Else
 If TextBox1.Value = "" Then ScannerInput1.Value = False
 End If
 
End Sub

I configured the scanner to use a preamble of "^".
The above code will set an option button to TRUE if it detects "^" as the first character input. Once the field is nulled out, it resets the option button to false.

I am not certain how I might want to use this feature in an application, but it seemed like a useful thing to know if a scanner is in use. Of course, if one manually keys "^" as the first character, then my form will think that a scanner is in use. I imagine there are other preamble characters that are not easy to enter via the keyboard.

As an interesting aside comment, when our Company decided to use UPC-A codes on our products, we discovered that one must officially register with
http://www.gs1-us.info/1-apply.html
which is a not-for-profit orginazation which issues/controls all UPC-A codes in North America.
This is necessary so that when a package of chewing gum is scanned in a store, it does not accidentally match a can of oven cleaner.

There is quite a bit to learn to be able to use UPC codes on packaging. And there is a database where you can enter any UPC code off a product, and find information about the Company which licenses that code.

Historical note of interest: The first product EVER to be scanned at a checkout register, when the technology was in its infancy, was a pack of Wrigley's gum.

Re: Error 91 With Scanner Input Only

Problem solved. (Sorry I am such a beginner).

Further searching and reading on this forum, and experimentation, helped me to see

Set rngfind = Selection.Find(What:=Trim(TextBox1.Value), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not rngfind Is Nothing Then
 rngfind.Select
Else
 MsgBox "not found"
End If

And using the code with the scanner has helped me to locate data entry errors which were resulting in the "not found" problem in the first place.

I need to develope this barcode scanning vba form for our small company. So I will appreciate any further tips, if you see something is lacking.

Thanks!

Re: Error 91 With Scanner Input Only

Upon further experimention (this is my first real VBA Excel Form), I discover that I was scanning some barcode that did not happen to be in the sheet, so I must need some kind of error code for when something is "not found". I created a second field, for the cursor to advance to (programming the scanner to add CR +LF), and in the "on enter" event of that field, I reset the first field to "" and do a setfocus on the first field. So I can keep scanning, and see the number found in the spreadsheet.

Private Sub TextBox2_Enter()
 TextBox1.Value = ""
 TextBox1.SetFocus
 
End Sub

I have a simple form with one text field, which works fine with the following code when I MANUALLY key in the number and press enter, but give a RUN TIME ERROR 91, if I scan the number in a press enter. The scanner works fine with Word and other apps, so it is talking to the machine ok. If I manually key in the 12 barcode digits and press enter, it works.

I have tried reconfiguring the scanner in various ways with regard to terminator = NONE, CR+LF, etc.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
 Range("A1").Select
 Columns("E:E").Select
'MsgBox TextBox1.Value
Selection.Find(What:=Trim(TextBox1.Value), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


End Sub