Excel VBA AutoFit Method

Setting  The Width Of Columns According To The Cell Content


          The Life-Saving Excel Feature You Probably Didn’t Notice.

By this code, Excel automatically adjusts the width of a column to accommodate the width of the text that takes up the most horizantal space in each row :
For this , we added that VBA codes to Data sheet Worksheet_Change procedure:
Columns(Target.Column).AutoFit




Read more ...

Excel VBA Create A Picture From Cells

Creating Image From Selected Cells And Saving

        An image is created  from the selected cell or cells. The generated images are saved to the in the same  location with workbook.

Image names are checked and each recorded image is saved with a different name.
For example : myimage1.jpg, myimage2.jpg


Codes that provide us to build the image:
Sub CopyRangeToJpg()
    Dim rng As Excel.Range
    Dim cht As Excel.ChartObject
    Dim alan As String
    Dim i As Long
   Dim strPath As String
   strPath = ThisWorkbook.Path & "\"
       Application.ScreenUpdating = False
    alan = Selection.Address
    For i = 1 To 1
        Set rng = Sheets(i).Range(alan)
        rng.CopyPicture xlScreen, xlPicture
        Set cht = Sheets(i).ChartObjects.Add(0, 0, rng.Width + 10, rng.Height + 10)
        cht.Chart.Paste
        cht.Chart.Export DosyaKontrolu(strPath, "myimage", ".jpg", i)
        cht.Delete
ExitProc:
        Application.ScreenUpdating = True
        Set cht = Nothing
        Set rng = Nothing
    Next
End Sub


The following function is used to check the image name and save the image with a different name:


Private Function DosyaKontrolu(DosyaYolu As String, DosyaOnEk As String, DosyaUzanti As String, Sayi As Long) As String
    Dim fso As Object
    Dim Kontrol As Boolean
    Dim TamDosyaYolu As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    With fso
        Do
          TamDosyaYolu = DosyaYolu & DosyaOnEk & Sayi & DosyaUzanti
           Kontrol = fso.FileExists(TamDosyaYolu)
           Sayi = Sayi + 1
        Loop Until Not Kontrol
        DosyaKontrolu = TamDosyaYolu
    End With
    Set fso = Nothing
End Function




myimage1.jpg



Read more ...

Excel VBA Listbox Selection Types And Copying The Selected Data

Copy /Transfer Selected Rows Of Multiselect And Multi Column Listbox To Range Of Cells On Sheet 



Excel VBA listbox selection types :
Single Item Selecting
     VBA code to select single item :        ListBox2.MultiSelect = 0  


 Multiple Items Selecting
      VBA code to select multiple item :    ListBox2.MultiSelect = 1  
➤ Multiple Selection By Pressing  keys
      VBA code to select multiple item by pressing Ctrl and Shift keys :   ListBox2.MultiSelect = 2  

   

Read more ...

Excel VBA Frame ControlEffect

Frame Lengthening And Shortening Effect With Buttons


The loops were used  purpose of lengthening / shortening frame ,stopping operation.


VBA codes of "Extend" button :
Private Sub CommandButton1_Click()
Dim i As Double
Cancel = False
i = Frame1.Height
    Do
        i = i + 0.015
        Frame1.Height = i
             If Cancel Then
                Exit Do
            End If
     DoEvents
        Loop Until Frame1.Height >= 276
End Sub


Read more ...

Simple Userform With Picture

Excel Simple Userform With Picture


          Userform that contained to display pictures with the next and previous record  buttons.
Important point is that ,names of the pictures with names of people in column A are same.
The picture with the same name as the value entered in the name texbox control (txtFirstName) is searched in the folder.

Codes to check the presence of the image in the folder:
With Cells(currentrow, 1)
txtFirstName.Text = Cells(currentrow, 1).Value
Set NameFound = .Find(txtFirstName.Text)

With NameFound
On Error Resume Next
imgData.Picture = LoadPicture(fPath & "nopic.jpg")
imgData.Picture = LoadPicture(fPath & txtFirstName.Text & ".jpg")
End With
End With

If recording don't have a picture ,"No Picture" (nopic.jpg) is displayed.



Read more ...

Price Quote Template

Price Quote Form Containing Image

          When product code is selected from combo boxes in Column B , product description, product image and product price are automatically assigned to the relevant cells.

I did not use Vba Codes in this template.Only I used formulas.



To delete data,  only  "-" you need to add into cell in column B.


Read more ...

Excel VBA Game

To Reveal Colors Or Couples 

        Reveal colors or couples, to find pairs with as few tries as possible.Double click with  your mouse to reveal colors or couples within the black cell

Once found, pairs stay visible!

Level of the game can be selected with Level toolbar.So that ,new rows and columns  can be added to game.




Read more ...