Solution is

Excel VBA: Detect arabic letters in cell

Tags: Excel VBA - Detect Arabic letters in cell - Detect non-English letters in cell

My case is: I have an Excel sheet which is updated daily. Each cell can contain Arabic letters only, English letters only, or both Arabic and English letters. Since I like specific font for Arabic "Sakkal Majalla" and specific font for English "Tahoma"  I used to edit fonts manually for each cell I edit (which was irritating and time-consuming) till I figured out how to simply automate this process with a simple tweak.

The following is a preview for "Sakkal Majalla" font:

The following is a preview for "Tahoma" font:

The next is VBA code which is simple (may be not efficient, but for me it worked like magic):

Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False For j = 1 To Target.Cells.Count   ' if multiple cells are edited once (by pasting cells from other place) If IsNumeric(Target.Cells(j).Value) = True And Target.Cells(j).Value <> "" Then    'if cell contains numbers only then it will contain zero characters Target.Cells(j).Font.Name = "Tahoma" Target.Cells(j).Font.Size = 9 Else ' cell contains text/sring OldFontSize = Target.Cells(j).Font.Size For i = 1 To Target.Cells(j).Characters.Count If InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789", Target.Cells(j).Characters(Start:=i, Length:=1).Text) < 1 Then With Target.Cells(j).Characters(Start:=i, Length:=1).Font         .Name = "Sakkal Majalla"         .Size = OldFontSize End With Else With Target.Cells(j).Characters(Start:=i, Length:=1).Font         .Name = "Tahoma"         .Size = 9 End With End If Next i End If Next j Application.ScreenUpdating = True End Sub

Excel nested If trick

Tags: Excel nested If trick, Nesting more than 7 IF statements in an Excel function, Alternatives To Nested IF Functions, How can I nest more than 7 IF statements together?, Simplified nested If.

Assume the following simplified case: we want cell B1 to have certain value based of the value of cell A1.

The psuedo code is:

If A1=10 then 

B1=A1*2

Elseif A1=20 then

B1=A1^2

Elseif A1=30 then

B1=3*A1^3

Else

B1=0

EndIf

Excel formula code placed in cell B1:

=IF(A1=10,A1*2,IF(A1=20,A1^2,IF(A1=30,3*A1^3,0)))

The previous formula called "Nested If" which means If statement inside another If statement.

For sure this Excel formula seems to be complex and it will get complicated by increasing the number of cases. In addition, Excel can't accept more than 7 nested If statments.

The tricky solution (without using VBA) is to use separate If statements summed together instead of nested If.  See the formula below

=IF(A1=10,A1*2,0)+IF(A1=20,A1^2,0)+IF(A1=30,3*A1^3,0)

The above solution is easy to read, revise, and edit later.

Try this solution and you will get the same result.

Excel hyperlink screen tip as comment

Tags:
Excel cell comment tricks
Excel cell hyperlink to make comment
Excel cell hyperlink screen tip to comment
Excel cell comment without arrow
Excel cell comment cell without top-right red corner triangle

The trick is done by creating a fake/dummy/useless hyperlink that refers to nothing (this can be done only programmatically), or you can create a hyperlink that refers to the cell itself using the "insert hyperlink" dialog.

To insert a hyperlink for a given cell, right click on the cell and press " Hyperlink...".

"Insert hyperlink" dialog will popup; Press "Place in this document" button on the left side, select the sheet, edit range, and finally press "ScreenTip..." button and write your text (comment).

After applying whatever the comment you want, it will look like the following capture below.

Programmatically, a single line of code is needed to do this :)

Disadvantages:

• Maximum number of characters per hyperlink screen tip is 256 characters

• You can't add new line using dialog. This only can be done programmatically using VBA code as shown before.

• You can't customize any thing in the screen tip: dimensions (width, height), font type, font size, font color, fill color, font effects (Bold, Italic, Underline),..., etc

• Screen tip has maximum width and maximum height limits. The example below show a multi-line screentip which is done using VBA code.

This is the code to generate the last screen tip.

Advantages:

• Extra parameter for saving data for cell

• No connecting arrow from comment to cell (personally, I don't like the shape of the arrow)

• The cell will not have top-right red corner triangle which in sheet full of comments with small cells will look horrible

• If cell is located at the most-left of your screen, screen tip will be shown in the visible region while the comment will not.

• Once the cell is deleted, hyperlink is removed, and the screen tip also is removed. In comments, you have to delete the comment manually.

• You can assign a screen tip to number cells at once by inserting the hyperlink for this range of cells.

It is done...

How to adhere bathroom mirror using silicon sealant

Tags:

Adhere bathroom mirror using silicon sealant

Mirror silicon application

Bathroom mirror installation

Fixing frame-less mirror

The following info-graphic shows how to fix bathroom mirror using silicon sealant based on YouTube videos of AkFix, Demsun, and some reversed engineering done by me.

Video links:

https://www.youtube.com/watch?v=yK97YN6TEN8

https://www.youtube.com/watch?v=Cq_7GgXJF8Q

Excel VBA: creating custom dialog (user form)

Tags: Word art in dialog sheet, Add word art to dialog sheet, Add shape to dialog sheet, Shape in dialog sheet, Rich text in dialog sheet, Add rich text in dialog sheet, Equation in dialog sheet, Add equation to dialog sheet, Add picture to dialog sheet, Picture in dialog sheet.


I am working with VBA user-forms for long long time and they unfortunately have ugly classic interface as you can see the user-form below). I also tried to solve this problem using Visual Studio, but I couldn't achieve that as I am not a professional programmer :)

I was always wondering how to create user-forms in MS Excel with nice looking controls till I discovered by accident what is called "Dialog sheets". In dialog sheet you can create a user-form assigned to workbook and add your own controls and assign macros to them. Here under, few quick steps to get it done in few minutes:

Step 1: Creation

1. Create an new workbook (or open an existing one). Right-click on any sheet tab to get a menu like in the image below

2. Click "Insert..." button, then you will get a dialog like the the one shown below

3. Select "MS Excel 5.0 Dialog" and press "OK" button. Then you will get a new sheet as shown below which has default name "Dialog1"

Step 2: Modification

1. Open the "Developer" tab, then press "Insert" button to be able to add "Form controls"

2. You can add and resize controls to get user-form like the one shown below

Step 3: Assign macros to controls

You can right-click on any control and select "Assign macro..." from the popup list.

Step 4: Calling (displaying) dialog

As an example, you can insert a "Command button" in :"Sheet1" and assign the following single-line macro to it which will display your dialog

Now you can click on the button "Display my dialog" and display the custom dialog (user-from) like magic!!!

Conclusion:

For me, dialog sheet form is very pretty, nice look, and visually-pleasant because its controls have the native theme of the operating system (Windows 7 in my case) Windows Vista or Windows XP, but it does not support the following controls which already available in VBA user-forms:

- Tab/notebook/multi-page control to display multiple pages
- Slider control
- Frame (with sibling controls)

One of the great advantages of dialog sheets that you can insert "Objects" in them like:

- ٍShapes!!! - Image
- Visio object (drawing)
- Microsoft equation 
- Word file
- etc

The following user form created by dialog sheet contains some interesting features (guess them out)...

The following is a trick, but it is a little bit useless. You can add a dialog inside another dialog by drag copying (not by copying and pasting) the dialog sheet.

Enjoy...

Access control MG236B wiring diagram

The following is a wiring diagram of access control system for a workshop area. 

The following electrical and electronic components are used:

- Access control with keypad & proximity cards plastic case MG236B

- Schneider relay base RXZE1M4C

- Schneider 12 VDC relay 8A1126B

- SMPS I/P 110~220 VAC & O/P +12 VDC / 4.2A

-  AKIS Solid State Relay 10A & 90-280Vac "ASR01-210AA" 

- 12 VDC electromagnetic door lock

- AC door bell

Future city flat plan

Future city flat (apartment) plan

Cairo-Ismailia desert road

Spanish district - 6th of October city - flat plan

Upgrading excel text filter

The following is a VBA programming concept for implementing a text filter for MS Excel sheets. The text filter has the following features:

- Uses ActiveX controls
- Instantaneous search and update
- Search for cells that contain words even if they are not complete
- Search for words regardless the case of the word: upper case, lower case, or mixed case
- Search for words regardless the order of them
- Search words are assumed to be space delimited
- Extra spaces are not considered

This is a demonstration captures:

After searching for word "shady":

After searching for "Mohsen Shad":

Here is the code below (sorry for adding it as a capture because I don't know how export it with colored keywords):

Good appetite :)