Posts by Karebac

    I have not visited this excellent forum in a while. I am recommending it to some programmers on Facebook. When I visited just now, I notice "I am a spammer". So, I logged in and updated my profile, and said NO to "I am a spammer." Just curious what this is all about.

    Re: Fill Alphabet Across Columns

    Thanks for the great tips, and also corrections/admonitions with regard to forum rules regarding titles, which I am slowly learning. My apologies for the spelling error. I now know to ask myself how my title might best contribute to the search engine.

    Today, I set myself the exercise to populate the first row with the 256 column names A,B,C...IV

    There are several lines of the code, in BOLD and UNDERLINED which seem extraneous and a resort to brute force. Is there a better (in the sense of more elegant) way to do this?

    I worked on this for some hours today, and might have posted here several times, with things that stumped me, such as errors when the subscript of an array is zero, or how to compute modulus, but I struggled and took this as far as I could on my own. I think it is working correctly now. If you un-comment the msgbox line, then you can watch it step cell by cell, which should be entertaining and instructive for other beginners.

    Re: First Cell In Range Not Searched

    Thanks, Reafidy! Now it works like a charm. I did start out with the macro recorder, and played with the code for about an hour, but I was missing the concept of

    With Worksheets(1).Range("a2:iv2")
    Set c = .Find(...

    which I subsequently found by searching

    That example code which I borrowed lacked all the arguments that you have provided.

    I am trying to understand how each argument works, and especially, which arguments did the trick to make my code search the first cell in the range.

    I searched in Excel Help, to read up in detail on what the find arguments might mean, but did not seem to find something useful.

    Range a1:iv1 (the entire top row), simply contains the column heading names A,B,C...AA,AB... IT,IU, IV (all possible 256 column names)

    Range a2:iv2 contains the series of numbers 1 through 256

    If you key in "B", the message box tells you "2"
    If you key in "2", the message box tells you "B"

    This is my own beginner's exercise.

    The following code seems to work for everything except the very first cell in the range.

    If I key in "A" the code returns 27, which is actually "AA".

    If I key in "1" the code returns "J" (so it is finding the leading numeral 1 in 10).

    The code is not searching the first cell in the range.

    How may I modify this code to include the first cell (A1)?

    I seek advice on using the value of NOW() as a record ID in an address book program.

    Question #1: Do Excel developers often use a record ID?

    Question #2: What record ID schemes are fequently employed besides date/time?

    I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.

    I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered.

    I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.

    Dim n As Double
     n = Now()
     sn = Str(n)
     p = InStr(sn, ".")
     first = Left(sn, (p - 1))
     l = Len(sn)
     d = l - p
     S = Mid(sn, (p + 1), d)
     f = Format(n, "Short Date") & " " & Format(n, "Long Time") & " " & Format(n, "m/d/yyyy h:mm")
     MsgBox sn & " " & first & " " & S & " " & f

    I feel I can use a NOW() datetime stamp not only as a source of an arbitrary record ID, but also for a field indicating date/time last viewed and a field showing when last modified.

    I shall value any tips or caveats regarding schemes for such a record ID in Excel.

    As a note of interest, I used an old version of Act! Contact Management with success for several years. When I took this new job, six months ago, I found that it was necessary to update to the latest version of Act! which by now was SQL based, rather than the Codebase xbase back end that Act! had used for so many years. I discovered that the new release was much slower, and also harder to use, than the old version I had been used to. A few weeks of study in Access produced something which was much faster and easier for me to work with, and also far easier to customize with fields and features unique to my company's needs. I am certain that if I develop the same Contact management application in Excel, that it will be easier still, once I master Excel and VBA.

    I have exported my Access Contact data to an Excel spreadsheet as a starting point for my project.


    Re: Tabstrip Vs. Multipage

    Thanks Reafidy! Your example is most helpful to me to visualize/conceptualize the utility of a tabstrip.

    I suppose, one tab in a multi-page could be "Comments", but on that page, a listbox might be embedded in a tabstrip, with tabs such as "Collections", "Quality Control", "Sales calls", and so one page of a multipage object, devoted to comments in general might, with one listbox, offer many different topics of comments.


    While teaching myself VBA, I wondered what a Tabstrip is and how it differs from a Multipage object.

    The tabstrip and multipage object curiously resemble one another, in that they both have tabs, and hence naturally arouses one's curiosity and raises this question.

    I searched this forum, and found one thread, from several years ago, where some senior members shrugged and said they had never used it. I found little in google, after an hour of searching, except for a Microsoft example which I completed and attached here.

    I still have questions in my mind about exactly when and how one might use a multistrip, but this example helps me a lot, so I felt it might help others as well.

    I am anxious to hear your tips and caveats about multistrips.

    A multistrip appears to be a handy way document related options for the programmatic alteration of the functionality of some aspect of an application, such as color, or font, or perhaps output device.

    Re: Subforms Vs Forms In Vba

    Thanks, all! I now have a better understanding of userforms, and the virtue of the multi-page control. I am basically just teaching myself excel and vba, and became cursious based on what I know of forms from Access.

    Re: Subforms Vs Forms In Vba

    I am familiar with the multi-page control, and you raise a good point. But out of curiousity, is one ill-advised to use more than one form, or have one form launch another? Are there ever circumstances where people do use several forms? Or should one always stick to the multi-page control?

    I have just now dutifully searched here, and elsewhere, on the topic of subforms in Excel VBA, and find nothing. Am I correct in assuming that one simply creates several userforms (and perhaps makes some modal), and launches one form from another? Are there any tutorials on guidelines for using several userforms at once? Thanks!

    Re: Sort Based On Two Independend Cells

    I am short on time, because I am at this annoying place called "work",
    but glancing at your post, I have the intuitive reaction that one might "build" a sort column out of any number of programatically concatenated values with VBA, and then sort the entire range on that contrived sort field. I used to do such in other programming languages like PICK basic, and RPGII.

    Just thought I would throw out that idea in support of the notion that any manner of sort is do-able, if you are willing to build a sort column programatically.

    Re: Macro To Capture Data From A Specific Cell Upon Selection Changes


    What you wrote in your spreadsheet attachment is a good illustration:

    I would like to have a code that upon my selection from A3 and A5 will capture the value from cell J21 (red pattern) and create multiple scenarios:

    Education Divorce Final Output
    Mid Yes 5
    Mid No 1
    High Yes 1
    High No 1
    Low Yes 1
    Low No 1

    As you can see this does not take a long time to do manually. However I'm planning to add more criteria that would make the manual selection and
    capturing of data very slow. I would apprecaite if you can help me.

    Re: Macro To Capture Data From A Specific Cell Upon Slection Changes

    I am a total beginner, but I downloaded your spreadsheet, and am having a look.

    I thought perhaps I would find some macros, or a user form, thinking that you problem might be with VBA execution, but I found none.

    So the first thought that comes to my beginners mind, is to create a user form with combo boxes that load with the various choices. After the user makes those choices, and clicks ok, then the VBA code proceeds to do all the things you desire. I am at work now, with limited time, but I shall try to play around with your spreadsheet, and will let you know if I come up with anything worth looking at.

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

    Questions are like Pringles chips; one often leads to another, which is why posts are called threads or sutras (which shares the same root as suture)

    And, as I said, I am addressing myself to the broad concept of protection and control in general, rather than the circumstances of the initial poster, whom I initially assumed to be some consultant developer, because I read in haste.

    I personally am not advocating that anyone do, or not do, anything with regard to protection of an application. I admire the spirit and philosophy of open source, and if I am ever able to create anything useful, I shall most likely place it in public domain.

    But it is interesting to explore the hypothetical question of how one might protect a spreadsheet application, or even IF one can protect a spreadsheet application. I worked with a number of software applications that used dongles since 1980, and the first one was on a Radioshack TRS-80 Model I.

    Of course, simple users would be mystified by the simplest of protection schemes, and the most sophisticated hacker could break anything.

    One would imaging that a highly skilled programmer, capable of breaking protection, would much prefer to re-write the application from scratch. It would have to be some fairly proprietary spreadsheet to warrant great measures of protection.

    Also, I have heard it remarked that most legitimate users WANT to be legal, and avail themselves of support, maintenance, enhancements, etc.

    Perhaps some of these posts will be of benefit to someone who feels they have some legitimate reason to protect their work with a dongle, or by making it accessible only on the Internet.

    And, at least one of my ideas, in my previous post, might inspire someone to design a new kind of program, that attempts to encapsulate the Excel application in some way. Or perhaps develop a way to take an Excel application with VBA, and compile it, or convert it to run on the Internet. I am sure Microsoft is capable of such feats, if they feel that there is some market for it. Perhaps someone at Microsoft, on the Excel team, will read this and undertake some project.

    It is similar to that Lottery motto: "If you don't play, you can't win." If we don't "play around with ideas" then we perhaps will not stumble across a new one.

    It is probably worthwhile having new keywords like dongle enter the search engines just to attract whatever traffic might be foolishly or wisely searching on such keywords.

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

    I received a reply from my inquiry to the KEYLOK folks, which suggests creating an add-in (so I searched here for how to create an add-in)

    Create an Addin

    I do think this is an interesting and useful topic in general, apart from the particular circumstances of the original poster.

    Dear Karebac:

    Thank you for your email inquiring about KEYLOK protection for Excel Spreadsheets. Protecting spreadsheets is a little tricky but it can be done in such a way to provide good protection. I have outlined the steps required from a high level.

    The scheme used to protect an Excel Spreadsheet using KEYLOK security dongle is broken into three basic

    parts, as follows:

    o Create a block of code that is inaccessible to the end user that

    checks for the presence of the KEYLOK security device.

    o If the security device is found then make accessible other functions

    within this secure block of code that are required for your

    application to work.

    o Force the call to the secure block of code upon loading of your

    spreadsheet, and protect this call from being altered or removed.

    Periodically check for the continued presence of the security

    device to prevent someone from running more than one copy of your

    program with a single device.

    Providing a secure block of code can be accomplished in a number of ways. Using passwords to protect the VBA code, using the read-only and/or hidden features or to obtain maximum security for the secure block of code it could be converted into an add-in.