Posts by srands

    Re: Database


    Quote from pankaj2610;642694

    Amazing, Really good lot of things seen for the first time, Keep it up


    Thanks, it's a great database, good looking, quick & easy to use, see HIDDEN sheet of inputUSERinstructions
    Can be edited from code/titles with relative ease, see HIDDEN sheet of RESULTScreateINSTRUCTION.


    QUICK START macro command buttons, all with no faults for SEARCH & ADD/EDIT/UPDATE/BROWSE, even includes photo preview in SEARCH & ADD/EDIT/BROWSE for records in database with photos inserted.


    http://www.1sar.karoo.net/QUICKstartFRONTdb.xls 390 KB's

    Re: Database


    NEWEST VERSION includes these instructions however hidden see below on how to reveal: http://www.1sar.karoo.net/DBuserform~ADD,SEARCH&EDITcars.xls 276 kb's

    MACROS: Obviously these spreadsheets have VB coding, hence will prompt to enable Macros on opening file, to do that:
    TOOLS, OPTIONS, SECURITY, MACRO SECURITY, MEDIUM, OK. Then close EXCEL, re-start EXCEL, re-open FILE again, select ENABLE MACRO.


    HIDDEN INSTRUCTIONS: FORMAT, SHEET, UNHIDE, .....INSTRUCTIONS....: Useful on how to edit spreadsheet, or create from new for your own use:

    Re: Database


    HOW TO EDIT THE SEARCH/EDIT USERFORM3 AMEND FIELDS FOR YOUR OWN PURPOSES:


    USERFORM3 OBJECT, on the far right side of the userform, are the auto populated AMEND FIELDS, just delete the LABELS boxes & TEXT boxes you no longer needed.


    USERFORM3 CODE, There are 3 sections within the code that need EDITING for the NUMBER OF COLUMNS to auto populate.


    Search code below for headings that start with:

    Private Sub cmdAmend_Click()


    If Not FoundIt Is Nothing Then


    Private Sub ListBox1_Click()

    Example: COLUMNS upto & inc AE that's 28 columns. You'll notice some refs are COLUMN +1, follow this pattern. The 1st row is an ambiguity hence 28 + 1 = 29


    Also USERFORM3 Window HEIGHT & WIDTH is changed within the code so the box fits the contents neatly, browse code for:


    If Not FoundIt Is Nothing Then ...... Me.Height = 425


    Private Sub ListBox1_Click() ...... .Width = 750


    Re: Database


    Quote from pankaj2610;642333

    Good improvement, but it create search record each time when we click Search button, It should not create the double search record.


    Thanks, the dbl entry on AMEND SELECTION is annoying. Perhaps somebody with more VB knowledge then me can decipher what code change is needed, then it really really would be a great spreadsheet.

    Re: Database


    Further revision to DB so the 2 USERFORMS, ADD and EDIT/SEARCH, are easier to edit for your own versions.


    http://www.1sar.karoo.net/DB~ADD,EDIT&SEARCHcars.xls 220 KB's


    Also populated data and fields edited to be car specific!


    HOW TO EDIT THE ADD RECORD USERFORM2 FOR YOUR OWN PURPOSES:


    USERFORM2 OBJECT, just change the LABEL not the TEXT BOX.
    USERFORM2 CODE, Just edit where your last column on data ends in spreadsheet DATA, for example if just 5 columns:



    HOW TO EDIT THE SEARCH/EDIT USERFORM3 FOR YOUR OWN PURPOSES:


    USERFORM3 OBJECT, just change the Option Button CAPTIONS not any of the TEXT BOXs.
    USERFORM3 CODE, To edit the COLUMNS searched edit the following column number in thw code between Select Case and End Select to your relevant COLUMNS:



    Comments:
    AMENDING ENTRY using USERFORM create dbl entry that must be deleted from DATA by deleting row.
    Search/Amend only displays 1st 6 rows, these fields are probably editable in the code above.

    Re: Database


    Quote from TarExcel;628962

    5 years on, and this is still of so much help. Wonderful tool. Thanks Jamie.
    Would you pls share the upgrade?


    Hi Folks, nice looking database, I've added to it, hope the original authors don't mind. I amalgamated the front sheet with ADD or EDIT/SEARCH feature that I copied from another spreadsheet:


    http://www.1sar.karoo.net/ExcelDB~ADD&EDITnames.xls 275 KB's


    Great spreadsheet, however:

    i). Needs DELETE records BUTTON adding into FRONT SHEET via seperate USERFORM (Or within SEARCH/EDIT)
    ii). AMEND SELECTION records (Search/Edit Record, search for surname example such as HOWDEN, left click surname found, Amend Selection appears on RIGHT HAND of USERFORM) creates double entry of same record (Pre-edit & After-edit), which is easily corrected by deleting row of Pre-edited record.

    Re: Distances and DriveTimes


    Hi I like that, I like that alot, so I've put it into a working file, with a few edits to make it easy for vb novices.


    Hence from XL file just enter POSTCODES and press a BUTTON, to make it so easy: http://www.1sar.karoo.net/DrivingDistanceCalc.xls

    Even simpler instructions
    From POSTCODE: A1
    To Destination POSTCODE: A2

    PRESS BUTTON "GET DRIVING DISTANCE" whilst connected to the internet! And then:


    MILES is displayed in F1
    TIME
    hh:mm:ss is displayed in F2


    Any objections from TOMTOM and the file hyperlink will be removed!

    Re: Database


    It's a nice DB file, it has a very nice front end display, however it's hard to edit the VIEW/EDIT RECORD, even from VB: FORMS, VIEWRECORD, I can't see this as a USERFORM, and the edit I've made to this code doesn't change the available fields.
    [ATTACH=CONFIG]50395[/ATTACH]
    How to edit that, is normally just edit USERFORM, txt boxes, command boxes, and corresponding COL, ROW & CELL REFs.


    HOWEVER I've also made a nice DATABASE myself it's very easy to use, with detailed edit instructions for your own use, share it around, easily give this a nice front with some buttons, it's also in the OPEN SOURCE, HEY THAT's COOL:


    http://www.ozgrid.com/forum/showthread.php?t=173211

    FILE hyperlinks below are spreadsheets of COMBO BOXs, both of these need editing:
    i) http://www.1sar.karoo.net/exoftable0a.xlshttp://"http://www.1sar.karoo.net/exoftable0a.xls" file size 119kb's MACRO version A, needs A-Z sort, needs relevant TYPE for MODELS of same MAKE
    ii) http://www.1sar.karoo.net/exoftable0ab.xlshttp://"http://www.1sar.karoo.net/exoftable0ab.xls" file size 120kb's MACRO version B, needs editing for relevant only. Blanks from COLUMN C cause inconsistency. And ROWS with same MAKE of different MODELS display TYPES of all that MAKE.

    Spreadsheet i)
    Is a macro A version, possibly, this just needs the properties of VB combo boxs editing for A-Z relevant, as well as error documented below. Currently in this version the only edit to the combo boxs, is edited from VB CODE/MACRO, PROPERTIES, in LinkedCell & ListFillRange.


    Spreadsheet ii) is a macro B version, it makes a simple error:
    For CAR MAKE (Column A) with numerous MODELS (Column B),
    then TYPE (Column C) displays all possible TYPES for that CAR MAKE (Column A).


    Eg: Vauxhall there are 2 entries:
    i) Vauxhall, Astra, VXR
    ii) Vauxhall, Nova, SRi


    When searching
    MAKE: VAUXHALL
    MODEL: ASTRA
    Then wrongly TYPE displays:
    SRI
    VXR


    INTRODUCTION TO PURPOSE OF SPREADSHEET:
    It is a simple DATABASE of CARS, with a RESULTS PAGE, much like an excel version of AUTOTRADER, a basic example without photos & descriptions.


    The Combo Boxs are:
    COLUMN A: CAR MAKE
    COLUMN B: CAR MODEL
    COLUMN C: CAR TYPE
    COLUMN D: CAR COLOUR


    The criteria of Combo Box's in 'ComboBox CHOICE & RESULTS', sorts results from 'WORKSHEET'.


    My spreadsheet looks ok, however the combo box's I've made lack the functionality I need, and to edit them further I'm unfamiliar with, hence they need editing to:


    i) Arrange COMBO BOXs to display in Alphabetical order, A to Z of available fields


    ii) or even better, arrange COMBO BOX A to Z, with no repetition


    iii) Combo Box's following from Previous, left to right, only to display relevant fields possible based on previous selections, not all fields in that column listed in COMBO BOX, PROPERTIES, ListFillRange, some examples below:


    Example 1:
    COLUMN A: If PEUGEOT is selected, then in
    COLUMN B: The only choices would be 206 or 207 or 306 or 307
    etc


    or


    Example 2:
    COLUMN A: If PEUGEOT is selected, then in
    COLUMN B: If 206 is selected, then in
    COLUMN C: only HDi would be available, then in
    COLUMN D: The only choices would be SILVER or BLUE


    Perhaps there are better, also easier to use spreadsheets, obviously the WORKSHEET spreadsheet is very good, and the autofilter is fast & effective at the same tasks described here, however I want a COMBO BOX spreadsheet without seeing the total full list of entries in the WORKSHEET.


    However I haven't seen/found or worked with such an example so far. My intention is that copies for other purposes can easily changed, by changing the titles, and then results worksheet sort is left unchanged hence will only display fields to choose from.


    The spreadsheet original for the 1st combobox is
    iii) http://www.1sar.karoo.net/exoftable.xls</a></strong>http://"http://www.1sar.karoo.net/exoftable0ab.xls" file size 111kb's non-MACRO version. 1st version.
    The above spreadsheet was derived from my FORMULA RESULTS spreadsheet below:
    iv) http://www.1sar.karoo.net/exof…ong></a><strong></strong>http://"http://www.1sar.karoo.net/exoftable0ab.xls" file size 451kb's non-MACRO version



    CROSSTHREADS:
    http://www.mrexcel.com/forum/e…relevant.html#post3327597

    http://www.excelforum.com/exce…s-of-words.html?p=3026357

    Re: FORUM webpages. USER PROFILES/MESSAGES? Attachments &amp; Avatars? XL db?


    Quote from celavey;608059

    I don't quite get it. What is this all about? [Blocked Image: http://imagicon.info/cat/14-15/icon_smile.gif%20]


    My question was originally how to have a FORUM ON YOUR OWN WEBSITE (3rd person perspective), FOR FREE.


    The obstacle is that many people with their own website, do so with free webspace, and to have a forum many of the codes available, a prerequisite IS to have a website domain with SQL (Structured Query Language) control panel.


    And most free/cheap web domain hosters don't offer that certain essential feature (SQL option to specify database name, username & password) for persons who have their own website. Fair enough SQL is at an extra cost, or go with another website domain host, I don't want to pay for just that feature.


    The solution being get a free forum elsewhere and just hyperlink it from your website (3rd person perspective).


    GREAT FREE HOSTING that doesn't need SQL HOSTING:
    http://www.pnyxe.com/compare-plans Scroll to the bottom, on the FREE column select 'START NOW'.

    Re: OFFSET Copy&amp;Paste Multiple Defined Named Ranges into 1 Worksheet


    I've resolved this myself with INDEX MATCH, essentially my workbook http://www.srands.co.uk/exoftable6.xls merges the RESULTS spreadsheets in MULTIRESULTS, which is great. The only comment I could make is that it's not in the order I'd prefer it in, the email order doesn't really matter, you'll see, later.My code copy&pastes as per data source entry (WORKSHEET), instead of per sorted RESULTS in spreadsheets RESULTS206 and RESULTScdTi, guess this is possible with OFFSET but nobody has replied to say if this is mission impossible, or possible, and how. Anyway it works, as follows: 'WORKSHEET' with 'predefined criteria' for


    ~ RESULTS206
    Column AF

    HTML
    =IF(AND(A2=0),"",IF(AND(G2="",OR(B2=206), H2="Y",I2="Y",J2="Y",K2="Y",L2="Y",M2="Y",N2="Y",O2="Y",P2="Y",Q2="Y",AC2="Y",AD2="Y"),"Y","N"))

    Column AG

    HTML
    =IF(A2="","",COUNTIF(AF$2:AF2,">="&RESULTS206!$U$2)-COUNTIF(WORKSHEET!AF$2:AF2,">"&RESULTS206!$U$3))


    ~ RESULTScdTi
    Column AI

    HTML
    =IF(AND(A2=0),"",IF(AND(G2="",OR(B2="civic"),H2="Y",I2="Y",J2="Y",K2="Y",L2="Y",M2="Y",N2="Y",O2="Y",P2="Y",Q2="Y",AC2="Y",AD2="Y"),"Y","N"))

    Column AJ

    HTML
    =IF(A2="","",COUNTIF(AI$2:AI2,">="&RESULTScdTi!$U$2)-COUNTIF(WORKSHEET!AI$2:AI2,">"&RESULTScdTi!$U$3))

    ~ MULTI-RESULTS (Of all results sheets)
    Column AL

    HTML
    =IF(AND(A2=0),"",IF(OR(AF2="Y",AI2="Y"),"Y","N"))

    Column AM

    HTML
    =IF(A2="","",COUNTIF(AL$2:AL2,">="&MULTIRESULTS!$U$2)-COUNTIF(WORKSHEET!AL$2:AL2,">"&MULTIRESULTS!$U$3))

    Then on 'SEPERATE' RESULTS SPREADSHEETS, and the COMBO 'MULTI-RESULTS':
    ~ RESULTS206
    Cell U4

    HTML
    =MAX(WORKSHEET!$AG:$AG)

    Cell A2 etc

    HTML
    =IF(ROWS($A$1:$A1)>$U$4,"",INDEX(WORKSHEET!A:A,MATCH(ROWS($A$1:$A1),WORKSHEET!$AG:$AG,0)))


    ~ RESULTScdTi
    Cell U4

    HTML
    =MAX(WORKSHEET!$AJ:$AJ)

    Cell A2 etc

    HTML
    =IF(ROWS($A$1:$A1)>$U$4,"",INDEX(WORKSHEET!A:A,MATCH(ROWS($A$1:$A1),WORKSHEET!$AJ:$AJ,0)))

    ~ MULTI-RESULTS (Of all results sheets)
    Cell U4

    HTML
    =MAX(WORKSHEET!$AM:$AM)

    Cell A2 etc

    HTML
    =IF(ROWS($A$1:$A1)>$U$4,"",INDEX(WORKSHEET!A:A,MATCH(ROWS($A$1:$A1),WORKSHEET!$AM:$AM,0)))

    So to my extravaganza finale (My purpose for this thread) in MULTI-RESULTS in Cell T1, left click the email symbol and emails autogenerated for all fields in MULTIRESULTS (Of all RESULTS), hooray, simple. http://www.srands.co.uk/exoftable6.xls

    [INDENT]I want to merge 2+ spreadsheets (Eg: RESULTS206 and RESULTScdTi) from same workbook into 1 worksheet (MULTI-RESULTS).


    Sample spreadsheet www.srands.co.uk/exoftable6.xls
    Note: RESULTS206 and RESULTScdTi are sort's of WORKSHEET1, then I want a MULTI-RESULTS of all RESULTS spreadsheets.


    Each spreadsheet has a named range, and the code I've entered for each of their defined name range's per RESULTS spreadsheet is:
    =OFFSET($A$1,0,0,COUNTA($A:$A),1) This code expands down as Many Rows as There are Numeric and Text Entries.


    Then to combine/merge all of the spreadsheets into 1 spreadsheet, copying row by row, of each RESULTS spreadsheet, TO EDIT [Blocked Image: http://www.excelforum.com/images/smilies/smile.gif]:
    =OFFSET(RESULTS206!A2,0,0,COUNTA(RESULTS206!A:A),1)


    =OFFSET(RESULTScdTi!A2,0,0,COUNTA(RESULTScdTi!A:A),1)


    What is the right code to combine the 2 formulas above into a 1 cell formula, that works? Sample spreadsheet: www.srands.co.uk/exoftable6.xls


    Guess this is straight forward, can't find any decent obvious examples, odd as surely many people encounter something like this on a daily basis.


    What I tried already :
    ~ Pivot tables, the summary of figures is NOT what I need.
    ~ VBA code I've tried, was not of any use, as would only copy&paste raw text and/or number, not formula generated data.
    ~ RDBmerge merging doesn't provide the solution I need.
    ~ Data, Consolidate does NOT work with words and text, only numbers.


    CROSSTHREAD(S):
    http://www.mrexcel.com/forum/showthread.php?t=632777
    http://www.excelforum.com/exce…ges-into-1-worksheet.html[/INDENT]

    Re: EXCEL to TXT SMS MSG's? VOIP / MOBILE PHONE / MOBILE BROADBAND



    Since none of the PROGRAMS/CODES mentioned offer a free trial even work in the UK, guess I'll browse in a couple of years and see if there is a working solution then.
    Well you need to see it working first, don't you, do I like this program, eh. :rolleyes:

    In my sample file http://www.srands.co.uk/exoftable5.xls 300kb


    2 Spreadsheets RESULTS P 206 and RESULTS H CDTI,have identical headings, all non-blank rows need merging / consolidating within the prepared 4th spreadsheet 'MULTI-RESULTS'.


    The data in the 2 spreadsheets will change, depending on the information available.


    However the consolidating feature of excel, excludes text/word entries (So sayes MicroSoft), the vba codes I've tried don't work, and the RDB solution isn't what I need.

    EXCEL to TXT sms messages, sounds simple doesn't it, so that you can automate txt's instead of typing in.


    In theory there are 3 possible ways, to send txt's from your PC/LAPTOP using your broadband connection:
    ~ VOIP:
    Over the internet, such as SKYPE, BUY TXT/TALK CREDIT


    ~ MOBILE PHONE:
    Via lead or blueooth or wirleess. Sends from PC/LAPTOP hence uses your MOBILE PHONE TXT credit, and also TXT SMS PROGRAM will charge per TXT.
    Hence may pay twice per TEXT MSG, which would put off alot of people.


    ~ MOBILE BROADBAND:
    Sends from PC/LAPTOP hence uses your MOBILE BROADBAND TXT credit, and also TXT SMS PROGRAM will charge per TXT.
    Hence may pay twice per TEXT MSG, which would put off alot of people.


    Of course if you still have an old fashioned telephone landline there EXCEL to HYPERTERMINAL hypertrm.exe, but landlines aren't mobiles, they are for phonecalls. EXCEL to VOICE PHONE CALLS via SPEECH function would have been a bit far fetched, but for somebody who writes VB code often, surely it would just be another COMMAND to add-in.


    SKYPE www.skype.com is the most obvious choice, the costs for just txts http://www.skype.com/intl/en-gb/prices/sms-rates/#listing-U are about 7p per text within UK. Seems reasonable. EXCEL SPREADSHEETS with VBA MACRO or EXCEL PLUG-IN to do this from SKYPE?


    OTHER PROGRAMS/EXCEL PLUG-IN's/EXCEL MACROs that actually work to send from EXCEL, txt msg's from your PC/LAPTOP via: VOIP, or MOBILE PHONEMOBILE BROADBAND?


    I've looked long and hard, at the TXT from your PC alterntives on offer, SKYPE doesn't work on my PC/3G network, AOL, YAHOO, MSN don't offer such services.
    Other then using your:
    MOBILE BROADBRAND pc program for single txt msg's, not bulk or automatic.
    or your
    MOBILE PHONE pc program for single txt msg's, not bulk or automatic.


    I've been pulling down hard on google and http://download.cnet.com/windows/ none of these EXCEL~TXT programs/add-in/spreadsheets work, not even those with free trial for a limited time, here is a list of those I've tried that haven't got such features, don't work as described, some were apparently demo only:
    mvaayoo (INDIA country only, shame nicest example I've seen) http://www.mvaayoo.com/excel_plugin.html
    Ozaki (VOIP doesn't work)
    SMSCO (VBA macro doesn't recognise specific mobile phone) http://www.smsco.it/tomcat/en/…o?resourceId=SMSLibX_demo
    CROWD SMS (Program demo only)
    SMART XL (Dubai only, Costs per Msg)
    PAGEONE EXCEL PLUG-IN (Broken link) http://www.pageone.co.uk/servi…ownload-plug-in-for-excel


    Of course there are many many more EXCEL->TXT creations which will be completely useless, just demo only, only for use in The Islands of the Hebrides or Outer Mongolia, login incorrect, or appears to work but the txt(s) you sent to yourself you never got, great test eh.


    SUGGESTIONS THAT WORK? COMPLETE SPREADSHEET(s)/PROGRAMS/MACRO'S/PLUG-IN'S, YOU'VE SEEN WORKING WOULD BE A START!


    Lets start with something simple, a spreadsheet that isn't a solution just a starting point: www.srands.co.uk/exoftable4.xls
    To START FROM on 4th tab 'LETTER' the first four columns (A to D) make up the txt conversation and the fifth column (E) is the mobilephone number of the person to be TXT'd.