Showing posts with label Web Control. Show all posts
Showing posts with label Web Control. Show all posts

January 14, 2011

Free Guestbook with Database

My brother asked me how he may be able to put a feature on his blog to allow guests to enter their details and have some form of database. So as I explored the way to do it, I thought that it will be a good feature for others' web site as well.

There are many free blog widgets/gadgets which are embeddable that one can find by simply googling "guestbook gadget", "embeddable guestbook" or such similar search word. Google has a site for their sponsored gadgets which you can view and then copy the embed code onto your blog. However, though simple, many of such gadgets are supported by banner ads, require membership to outside data company or may not generate data at all.

So for this endeavor, I decided that more personal control is required. Such feature is preferably without a need to go outside google (since this blog is hosted by the same).

The steps are:

1. Creating a google spreadsheet to serve as container of the guest-details and the capture form.

2. Have a google blog to serve as the gateway site where you can invite those casual surfers and guests to register. This is where the embed code of 1 above is used. Preferably you have to offer something worthwhile for their effort to provide their details.

3. Have a separate restricted site where those you have accepted can be invited or be given their access code via their email.

- - - -
Now let's explain the above three steps in more detail:


1. Google Spreadsheet

Google documents is available as a drop down menu under "More" on the google initial page.




Here's a tutorial to let you view how it's done. But for your purpose, you may have the following table headings: a) First Name b) Last Name c) Email Address d)Location. Please bear in mind in the following video, the tutor went directly to form, so he was limited to the data available in the wizard. For you, just enter the mentioned table headings a) to d) first on the spreadsheet. So when you click the form menu, such data will be available:



Copy the embed code provided by the form to a notepad document. You will paste that code in the body of your welcome/gateway page.


2. Google Blog
Here is one tutorial for setting a blog hosted by google. I find their blog, simple enough but full featured compared with others I have tried.




Before we go to no. 3


Here's a picture of my sample google spreadsheet:



or go here to view it in real time. You may also notice that google automatically added a column for "Timestamp".

Here is the Guestbook as embedded (I tweaked the theme a bit - themes are available on the google doc form menu):



I accidentally deleted the "Last Name" from the "Form Menu", but for sample purposes, it may not matter. It points out also that you may add additional column to tag which valid e-mail address were already replied with.

Enter details on the above form and refresh the real time view of the google documents to see if data is forwarded accordingly.

Bear in mind that in your case, you can restrict viewing of the said spreadsheet so no one else can view the database captured.

Not all will fill 'em up with the right data. So you have to weed and trim your data spreadsheet container by deleting some irrelevant rows. Your reply to their email address is a security measure so that they will fill-in at least a valid e-mail address.


3. Restricted Site

For this purpose, there are several alternatives:

A. Creating a Google Group Site: On the google initial page, under the drop down menu "More" the . Google have templates which are for members of a group. Obviously, that will be limited to those who have valid email accounts. If you are logged in to your google account, you can access the google site.


I have tried one template - see it here. You can restrict it as follows:

These templates are pre-filled in a structure of several pages, the contents of which may be time-consuming to be populated or edited if you have few available materials or information to work with.

B. Host the restricted site on your computer (where you have all the controls):

Opera and Firefox have add-ons to let you share pages/files from your computer. I find that Opera Unite is simpler to manage/secure but if you want to have the flexibility of scripting, Firefox's "POW" is more programmable.

POW is available here. While, if you have a fairly recent version of Opera, the server is activated by an icon on the sides or bottom (resembling fan blades).

Another way to host your files and share it to the net is by downloading a limited version of "Badblue". Using this is very simple but there are some disabled features for the free version. I found a download page here.

C. Pay for a hosted restricted site. You may already have some of these corporate or personal sites for which you pay regularly and consequently let some admin take care of security. You can simply e-mail the locations of these sites and the related access codes.

D. KeepandShare, Rapidshare, Hotfile etc. You may also convert your documents into PDF format and upload them to free and paid file hosting services. You can download "PrimoPDF" which installs for you a printer that outputs whatever you print into PDF format. You can then upload these to the file hosting service and email the links to your bonafide guests. However, you won't have much control once they downloaded the files.

_____________

Sample of more simple guestbooks:
a. Slide
b. more here
c. the Google gadgets are here. But for guestbook, you have to search because a ton of all sorts are available there (not only guestbooks).



Notes:

- As the google form is created by their wizard, there seems to be no way to infuse in them some validations or scripted actions (for example, refusing to move to another field until a required field is filled up).

- I tried KeepandShare a little and they offer good services. There are email notifications when someone downloads your shared files. I have not tried Rapidshare and Hotfile, but I guess they are fairly common.


.ciao and have fun

June 29, 2010

Web Control Office on Spreadsheet

Re: My previous post about web control embedded in a spreadsheet, there are some slight variations when using office 2007 or 2010 version. The "Visual Basic" toolbar is replaced with the "Developer" tab in the ribbon area. You may have a hidden 'Developer' tab in the ribbon so you may refer to microsoft on how to do show this tab. Otherwise the steps and code will just be the same. Except perhaps if you opted to customize your spreadsheet application a bit.

I have uploaded a version with some customization. You may find it here: Browser.xls

As you can see, I have changed the spreadsheet background and reworked the code to show a theater screen curtain when the toggle button captioned 'power' is turned off. Of course it is also pulling the image from the web so don't expect the screen to show the intended URL if you are offline.

I have left open the VBA portion (you can view it by pressing 'Alt-F11') so you can further modify it. You can load it initially with macro disabled to check that there is no malicious code therein. You may also put additional controls similar to media player buttons to go to the next or previous URL, for example these button types: Media-Player-Buttons.

Some techniques we have employed in creating the said application will be helpful as I post other similar topics. The data validation formatting of a cell, the range name that refers to a dynamic list and the 'vlookup' formula. These will be useful as I will be posting later how to make an Excel front end to a database system.

I am planning to post how to use Excel in Accounting. Excel is particularly adept at the complexities required in business and to use it in the generation of the books of account is ideal. However, I will call in a more robust database component for maintaining the records. It will be a good learning experience for me and for you.

Keep visiting this blog for more!

June 26, 2010

Embedded Excel Web Control

Embedding a browser in a spreadsheet can be a fun way to send your friends and associates the browsing experiences you need to share them instead of creating a PowerPoint file. You can use this to aggregate your different blogs, file sharing, facebook, youtube and other accounts into a cohesive and sequential browsing.

In case your Excel is not yet updated version, I will share you the way to do this and still, you can use Excel 2007/2010 though the screen can be different a bit.

1) Open a blank worksheet. Right click any part of menu or icon and select cutomize and from the ensuing toolbar customization dialog- check 'Visual Basic'.

2) Now click the "Control Toolbox' icon (the one that looks like a hammer and a wrench) and then you have the option to embed various control using the last toolbox icon. Click it and scroll down to select "Microsoft Web Browser'. Then click the spreadsheet area to place it into. You may arrange and re size it, but leave 3 rows from the top.


3) Click the "Control Toolbox' icon and select the text box control. Place it on cell 'F1'. Right click it, and select view code. You will be taken to the coding window.

4) Paste the following lines of code:

Private Sub TextBox1_Change()
WebBrowser1.Navigate TextBox1.Text
End Sub

5) Now go back to the spreadsheet and select sheet2 tab and enter the following starting with cell 'A1':

Site Address
Google http://www.google.com/
Journey http://www.youtube.com/watch_popup?v=AO0LtxIpujM
PGT http://www.youtube.com/watch_popup?v=HwrcBVb_RJg
IndiArtist http://www.isound.com/kristen_rini

6) Now click the menu 'Insert', 'Name' and then select 'Define'. From the dialog, accept the name site and paste the following as the cell range: "=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1)". The range name "Site" will then be dynamically accommodating the list size of url as you edit, add, delete later. (The list must be a contiguous downward list).


7) Go back to sheet1 and select cell 'B2'. Click the menu ' Data' then select 'Validation'. On the dialog, select "List" on the dropdown box. Enter "=Site" on the box labeled 'Source'. Now 'B2' will have a validation that will list the sites from the dynamic range: "Site" (which is a list contained in sheet2).

8) Input the following formula (without quotation marks) in cell 'D2' : "=VLOOKUP(B2,Sheet2!A:B,2,FALSE)"

9) Right click the textbox control on cell 'F1' and select properties. On the list provided, select Linkedcell and input 'D2'. Also select the property 'Visible' and turn the value to "False"

10) Now click the icon named "Exit Design Mode" from the "Visual Basic Toolbar". Select cell 'B2' and change to see whether the browser control show the intended websites. Save the file after edit of the desired url listing on sheet2.

You may then prettify the workbook by changing the background color/picture, putting the necessary captions on cell 'B1' & 'D1', rename sheets, refine the validation rules, etc. You may also rename the browser control by going into design mode, but adjust the VB code accordingly.

Now whenever, you can say your point thru the web pages you view, then you can sell or share that point and let your friends experienced it being pulled from the web. Enjoy!