Posts by yjoshi

    Now I get your problem,


    Its very much possible....


    If you attach a sample of your stuff, or even a dummy one, i can fix it for you.


    I currently have some such applications working for me in my office, but cannot give a generic code without understanding the requirement.


    Plese post back..... and if it is not possible to share your work with us, then post accordingly, i will share mine with you.

    I can understand your frustration, but it really workes for me and never faced any problem with this.


    I have attached a sheet which has all the instruchtions, in the merged range.


    This serves two purposes, gives you the steps and shows you how it worked for me :)


    :thumbcoo:

    Hello,


    Recently, saw a lot of post on how to handle, transfer and copy data from one sheet to another.
    Also... I had promised Dave in one of my early posts, that I will contribute a small but smart application in Excel, and I am trying to live up to it. Don't know how much successful I am.


    Here is a small Application in Excel which includes:

    1. Showing Form-like structure without using form
    2. Hiding all traces of Excel when the application is activated
    3. Enabling menus and commandbars when the file is closed or deactivated
    4. A complete Data Entry and Data Retrival system for a table with 6 fields
    5. A complete and Full-proof Navigation system through the data, alongwith display of record position
    6. Add, Edit, Delete Records with proper enable / disable / hide / unhide of buttons and the entry fields
    7. Different colour scheme for entry and view mode.


    The code, though huge is pasted below, hope this will be useful to those interested. Also attached the application for your use.


    Please be kind enough to send your suggestions / comments through U2U or mail.


    Hope i can correct my code in the process and get some more ideas :)


    ============================================================


    Sub Workbook_Activate()


    Run Sheets("Form").wbk_activate()
    End Sub
    Private Sub Workbook_Deactivate()


    Application.CommandBars.ActiveMenuBar.Enabled = True


    With ActiveWindow
    .DisplayHeadings = True
    .DisplayHorizontalScrollBar = True
    .DisplayVerticalScrollBar = True
    .DisplayWorkbookTabs = True
    End With

    With Application
    .DisplayFormulaBar = True
    .DisplayFullScreen = False
    .DisplayFormulaBar = True
    End With



    End Sub


    Private Sub Workbook_Open()
    Sheets("Form").Select
    Range("Sr").Select
    Range("Sr").Value = 1
    ActiveSheet.unprotect
    ActiveSheet.EnableSelection = xlUnlockedCells
    Run Sheets("Form").populate()
    Run Sheets("Form").protect_rng()
    ActiveSheet.protect
    Worksheets("Form").ScrollArea = "B3:H50"
    Range("Name").Select


    End Sub



    Sub protect_rng()


    Dim data_disp(6) As String


    data_disp(0) = "Name"
    data_disp(1) = "Ext."
    data_disp(2) = "Mail_UID"
    data_disp(3) = "Machine"
    data_disp(4) = "AIM_id"
    data_disp(5) = "Resp"


    ActiveSheet.unprotect


    For x = 0 To 5
    With Range(data_disp(x))
    .Locked = True
    .Interior.ColorIndex = 20
    End With
    Next


    ActiveSheet.protect


    End Sub
    Sub unprotect_rng()
    Dim data_disp(6) As String


    data_disp(0) = "Name"
    data_disp(1) = "Ext."
    data_disp(2) = "Mail_UID"
    data_disp(3) = "Machine"
    data_disp(4) = "AIM_id"
    data_disp(5) = "Resp"


    ActiveSheet.unprotect


    For x = 0 To 5
    With Range(data_disp(x))
    .Locked = False
    .Interior.ColorIndex = 19
    End With
    Next
    'Run Sheets("From").disable_navig()
    ActiveSheet.protect


    End Sub
    Sub clear()


    Dim data_disp(6) As String


    data_disp(0) = "Name"
    data_disp(1) = "Ext."
    data_disp(2) = "Mail_UID"
    data_disp(3) = "Machine"
    data_disp(4) = "AIM_id"
    data_disp(5) = "Resp"


    ActiveSheet.unprotect


    For x = 0 To 5
    Range(data_disp(x)).Value = ""
    Range(data_disp(x)).Locked = True
    Next


    Range("Name").Select


    ActiveSheet.protect
    End Sub
    Public Sub populate()


    Dim pos As Long
    Dim data_disp(6) As String
    ActiveSheet.unprotect
    pos = Range("Sr").Value


    data_disp(0) = "Name"
    data_disp(1) = "Ext."
    data_disp(2) = "Mail_UID"
    data_disp(3) = "Machine"
    data_disp(4) = "AIM_id"
    data_disp(5) = "Resp"


    For x = 0 To 5
    Range(data_disp(x)).Locked = False
    Range(data_disp(x)).Value = Sheets("Data").Range("A1").Offset(pos, x + 1).Value
    Range(data_disp(x)).Locked = True
    Next


    'Sheets("Form").Select
    'Range("Name").Select
    ActiveSheet.protect
    End Sub


    Private Sub Cmd_Cancel_Click()


    Dim res As VbMsgBoxResult
    res = MsgBox("Do you want to descard the changes you have made?", vbYesNo, "Cancel Changes")
    If res = vbYes Then


    Sheets("Form").Range("Sr").Value = Sheets("Form").Range("On_Cancel").Value
    Sheets("Form").Range("On_Cancel").Value = ""
    Run Sheets("Form").populate()
    Run Sheets("Form").protect_rng()
    Cmd_Cancel.Enabled = False
    Cmd_Cancel.Visible = False
    Cmd_Edit.Enabled = True
    Cmd_Add.Enabled = True
    Cmd_Del.Enabled = True
    Cmd_Save.Enabled = False
    Run Sheets("Form").validate_navig()
    End If


    End Sub


    Private Sub Cmd_Close_Click()
    Dim res As VbMsgBoxResult



    res = MsgBox("Do you want to Exit the Application?", vbYesNo, "Exit Decision")


    If res = vbYes Then
    Run Sheets("Form").wbk_deactivate()
    ActiveWorkbook.Close (Savechanges = True)
    End If


    End Sub


    Sub Cmd_Edit_Click()
    Sheets("Form").Range("On_Cancel").Value = Sheets("Form").Range("Sr").Value
    Run Sheets("Form").unprotect_rng()
    Cmd_Edit.Enabled = False
    Cmd_Add.Enabled = False
    Cmd_Del.Enabled = False
    Cmd_Cancel.Enabled = True
    Cmd_Cancel.Visible = True
    Cmd_Save.Enabled = True
    Run Sheets("Form").disable_navig()
    Range("Name").Select



    End Sub


    Sub Cmd_Del_Click()
    Dim res As VbMsgBoxResult
    Dim pos As Long
    pos = Range("Sr").Value
    res = MsgBox("Do you want to delete record for " & Range("Name").Value, vbYesNo, "Record Deletion")


    If res = vbYes Then
    Sheets("Data").Range("A1").Offset(pos, 0).EntireRow.Delete
    Run Sheets("Form").cmd_Prv_Click()
    End If


    End Sub


    Sub Cmd_Add_Click()
    Sheets("Form").Range("On_Cancel").Value = Sheets("Form").Range("Sr").Value
    Run Sheets("Form").clear()
    Run Sheets("Form").unprotect_rng()
    Range("Sr").Value = Sheets("Form").Range("cur_max").Value + 1
    ActiveWorkbook.Save
    Cmd_Edit.Enabled = False
    Cmd_Add.Enabled = False
    Cmd_Del.Enabled = False
    Cmd_Cancel.Enabled = True
    Cmd_Cancel.Visible = True
    Cmd_Save.Enabled = True
    Run Sheets("Form").disable_navig()
    Range("Name").Select


    End Sub



    Sub Cmd_Save_Click()


    Dim pos As Long
    Dim data_disp(6) As String


    If Trim(Range("Name").Value) = "" Then MsgBox "Please enter Name": Exit Sub


    pos = Range("Sr").Value


    data_disp(0) = "Name"
    data_disp(1) = "Ext."
    data_disp(2) = "Mail_UID"
    data_disp(3) = "Machine"
    data_disp(4) = "AIM_id"
    data_disp(5) = "Resp"
    Sheets("Data").Range("A1").Offset(pos, 0).Formula = "=row()-1"
    For x = 0 To 5
    Sheets("Data").Range("A1").Offset(pos, x + 1).Value = Range(data_disp(x)).Value
    Next


    Run Sheets("Form").protect_rng()
    'Sheets("Form").Select
    'Range("Name").Select


    Sheets("Form").Range("On_Cancel").Value = ""
    ActiveWorkbook.Save
    Cmd_Cancel.Visible = False
    Cmd_Cancel.Enabled = False
    Cmd_Save.Enabled = False
    Cmd_Edit.Enabled = True
    Cmd_Add.Enabled = True
    Cmd_Del.Enabled = True
    Run Sheets("Form").validate_navig()
    End Sub


    Sub Cmd_First_Click()


    Range("Sr").Value = 1


    Calculate
    Run Sheets("Form").populate()


    Run Sheets("Form").validate_navig()


    End Sub


    Sub cmd_Last_Click()


    Range("Sr").Value = Sheets("Data").Range("Data_Sr").Count - 1


    Calculate
    Run Sheets("Form").populate()


    Run Sheets("Form").validate_navig()
    End Sub


    Sub cmd_Next_Click()
    If Range("Sr").Value < Sheets("Data").Range("Data_Sr").Count - 1 Then
    Range("Sr").Value = Range("Sr").Value + 1
    End If
    Calculate
    Run Sheets("Form").populate()


    Run Sheets("Form").validate_navig()
    End Sub


    Sub cmd_Prv_Click()


    If Range("Sr").Value &gt; 1 Then
    Range("Sr").Value = Range("Sr").Value - 1

    End If


    Calculate
    Run Sheets("Form").populate()


    Run Sheets("Form").validate_navig()
    End Sub



    Sub disable_navig()
    Cmd_First.Enabled = False
    cmd_Last.Enabled = False
    cmd_Next.Enabled = False
    cmd_Prv.Enabled = False
    End Sub


    Sub validate_navig()
    cmd_Next.Enabled = True
    cmd_Last.Enabled = True
    Cmd_First.Enabled = True
    cmd_Prv.Enabled = True


    If Range("Sr").Value >= Sheets("Data").Range("data_Sr").Count - 1 Then
    cmd_Next.Enabled = False
    cmd_Last.Enabled = False
    Cmd_First.Enabled = True
    cmd_Prv.Enabled = True
    End If

    If Range("Sr").Value <= 1 Then
    Cmd_First.Enabled = False
    cmd_Prv.Enabled = False
    cmd_Next.Enabled = True
    cmd_Last.Enabled = True
    End If


    End Sub


    Sub wbk_activate()



    Application.ScreenUpdating = False

    Application.CommandBars.ActiveMenuBar.Enabled = False

    With ActiveWindow
    .DisplayHeadings = False
    .DisplayHorizontalScrollBar = False
    .DisplayVerticalScrollBar = False
    .DisplayWorkbookTabs = False
    End With

    With Application
    .DisplayFormulaBar = False
    .DisplayFullScreen = True
    .DisplayFormulaBar = False
    .ScreenUpdating = True
    End With


    End Sub


    Sub wbk_deactivate()


    Application.CommandBars.ActiveMenuBar.Enabled = True


    With ActiveWindow
    .DisplayHeadings = True
    .DisplayHorizontalScrollBar = True
    .DisplayVerticalScrollBar = True
    .DisplayWorkbookTabs = True
    End With

    With Application
    .DisplayFormulaBar = True
    .DisplayFullScreen = False
    .DisplayFormulaBar = True
    End With


    End Sub

    Interesting ..... Very Interesting in fact.


    Will it be feasible to share your work with us???


    It will be a good value addition to us as well, you have a very high of getting the answer, making use of some of the Expert VBA scripts ???


    I am ready to put some effort in this, though i have not worked on such a stuff till date.
    :beergrin:

    Is it regarding the right,left margins or the header margins???


    If i am not wrong, it is about the header margins, because, in htm, there is no right margin concept involved.


    Yes about the upper margin, this happens because when excel creates an HTM page it creates pretty badly (My personal experience after working extensively with this feature, if anyone has better, please share your good experience)


    If that is what troubling you, try code and suggestions in following link, it works really fine, and you will really understand, why the excel to htm process is really bad one. (Nothing personal about existing process, but it generates lot of crab html :()


    http://myweb.tiscali.co.uk/xlvba/excelvba/tips/index.htm


    Hi Dave, Could not find anything on ozgrid with "export+html" search


    This kind of stuff is worth having on our site as well, What Say???

    Hi Buddy,


    I thought only i faced such problems, but there are more. I think it is worth now to put it in Hay this is cool::


    IF by mistake F8 key is pressed, which means : Turn on extending a selection by using the arrow keys this will certainly happen.


    What it does is that it acts as if you have pressed shift key and keeps adding the range.


    Also if you press Shift+F8 it acts as if you have pressed down a Ctrl key while clicking.


    If you have tried shaking well , hitting lightly, hitting lightly and even your mouse thin this must be the reason i feel.


    What say???

    If i have guessed it right you need something like this.


    1. You have certain cells that are merged through rows
    2. In this merged region, you want user to insert multiple rows.


    There are two different solutions:
    1. Go to Cell Format and In Alignment, click on wrap text, which will allow the user to enter free flowing text.
    2. When you are entering a line and you want to start on next line in same cell (merged cells for that matter), press Alt+enter, which acts as a line-feed.
    When you do this, the alignment automatically gets wrap text property as true. :D


    HTH

    Highly confusing:puzzled:


    If you save a wrokbook, everything will be saved. no option to save partial data.


    However, as you have not given the sample of your data, cannot assist more here.


    But it is possible to copy certain data only to another file and save, if that is want then post with example and i am sure you will get some help :) :thumbup:

    This code will give you what you say you want :)


    Private Sub cmd_copy_data_Click()
    'By Yogendra Joshi
    Dim sht As Worksheet
    'Clear Existing Data
    Sheets("_Overall").Range("A7").Select
    If (ActiveCell.SpecialCells(xlLastCell).Row) &gt; 6 Then
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Clear
    End If


    'Checking for Worksheets and copying data
    For Each sht In Worksheets
    If Left(sht.Name, 1) <> "_" Then
    With sht
    .Activate
    .Range("A7").Activate
    If (ActiveCell.SpecialCells(xlLastCell).Row) &gt; 6 Then
    .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy _
    Destination:=Sheets("_Overall").Range("A65536").End(xlUp).Offset(1, 0)
    End If
    End With
    End If
    Next
    Sheets("_Overall").Activate
    End Sub


    I have tested with lot of crab data, and also encoded the checking for sheets where there is no data, so that the wrong data does not come in!!!


    Also attached the sheet.


    Hope this helps.


    Also please avoid bringing in toooo many issues in one quote. Open different postes for different issues. This will avoid the quotes getting too bulky and confusing.


    What say???

    I can assure that you made me work hard.
    But no problem. It was interesting.


    All the work is completed except:
    1. Inputbox: for dates ( Surely this is feasible, but is it really required?? Cant it be included in the source itself? If yes, no probs. I will do it for you, or you can also amend the code
    2. Different workbooks: First see if the things you want are satisfied, doing this is not a big deal.
    3. False / True in the target Sheet: not filled as do not know the criterias.


    this is the code, also the file attached:


    ****************************************************************************
    Private Sub Worksheet_Change(ByVal Target As Range)
    'This code will check the value in Post? Column, based on Yes
    'will paste the data in the sheet
    Dim sht As String
    Dim s As Worksheet
    Dim date_Dec As Boolean
    date_Dec = True



    '========= Part I : checking range and data entered =========
    'This will check if the value changed is in D column
    If Target.Count <> 1 Then Exit Sub


    If Intersect(Target, Range(ActiveWorkbook.Names("Posting"))) Is Nothing _
    And Target.Value <> "Yes" Or Target.Value = "" Then Exit Sub


    ''========= Part III : Transfer of data to appropriate places =========
    Application.ScreenUpdating = False


    'this selects the next available row
    With Sheets("Target")
    .Activate
    .Range("A65536").End(xlUp).Offset(1, 0).Select
    End With


    With Sheets("source").Range(Target.Address)
    Selection.Value = Year(Now())
    Selection.Offset(0, 1).Value = "AGAL"
    Selection.Offset(0, 2).Value = .Offset(0, -13).Value
    Selection.Offset(0, 3).Value = .Offset(0, -12).Value
    Selection.Offset(0, 5).Value = Mid(Trim(.Offset(0, -11).Value), 17, 3)
    Selection.Offset(0, 6).Value = Right(.Offset(0, -11).Value, 1)
    Selection.Offset(0, 9).Value = Replace(.Offset(0, -9).Value, "<", "")
    Selection.Offset(0, 11).Value = Replace(.Offset(0, -8).Value, "<", "")
    Selection.Offset(0, 13).Value = Replace(.Offset(0, -7).Value, "<", "")
    Selection.Offset(0, 15).Value = Replace(.Offset(0, -6).Value, "<", "")
    Selection.Offset(0, 17).Value = Replace(.Offset(0, -5).Value, "<", "")
    Selection.Offset(0, 19).Value = Replace(.Offset(0, -4).Value, "<", "")
    Selection.Offset(0, 21).Value = Replace(.Offset(0, -3).Value, "<", "")
    Selection.Offset(0, 23).Value = Replace(.Offset(0, -2).Value, "<", "")
    Selection.Offset(0, 25).Value = Replace(.Offset(0, -1).Value, "<", "")
    End With
    With Sheets("source")
    .Activate
    .Range(Target.Address).Select
    .Range(Target.Address).Offset(0, 1).Value = Now()
    End With
    Application.ScreenUpdating = True


    End Sub


    ****************************************************************************


    Best of Luck......

    In that case, what you have devised is really good. It takes care of most of the stuff. Yours is really a full-proof formula though it is a bit slow.


    BRAVO.

    How about this???


    =LEFT(RIGHT(TRIM(A1),8),6)


    Will only work, if the last three chars are " °C" and the number is in format 99.99


    how do you think this will help you???

    *Look at text rather than numbers (eg "Vehicle One" rather than "1")


    -&gt; If the number of vehicles is small say 5-10 then this is a good idea as well.
    You can create a data validation drop down list, use the code i gave first and use "select case" instead of "if = 1" etc.


    Let me know if you are not comfortable with Select Statements.


    *Copy only some fields from the row


    Yes, very much true:: Following code will
    copy only 4 cells in that row (Target and three before that)


    Range(Target.Offset(0, 3), Target).Copy


    replace the copy statement with this or an amended version of this!!!


    * There is a further condition - I only want to copy the rows that I entered that day.


    Humm.... This is interesting now.
    What you will need to do is that::: in some cell in the row, you will need to put the change date in that cell in every row.


    ActiveSheet.Cells(Target.Row, 100).Value = Now()


    This statement at the start of the code in
    Sub Worksheet_Change(ByVal Target As Range) will enter the date (time as well)
    You will need to compare it at the time of transfer.


    Hope this helps you.


    Regarding the last point, if you find it difficult to convert, post back with sufficient details!!!

    Quote

    Originally posted by Chris Davison


    have you tried "assume linear model" in options ?


    (hey we can have our own Forum : Solver Idiots :guitar: )


    Ya!! I tried everything, when it cannot solve, it conveniently breaks the rule of integer, and making all the formule or Match, countif, count etc. go hey-wy



    Can i suggest a better name???


    :smash:Idiot Solvers :smash: