VBA help Required

Question

// Fills AR details in Alfresco from pasted Excel rows.
// Columns per row (TAB-separated from Excel):
// Action | FirstName | MiddleName | LastName | Suffix | JobTitle | Country | CountryOther | PhoneType | PhoneNumber | Email | Role
function fillAR(rawText) {
  // ---------- Parse Excel-like tab-separated text ----------
  function parseRaw(rawText) {
    const lines = rawText
      .trim()
      .split(/\r?\n/)
      .map(l => l.trim())
      .filter(l => l.length > 0);
    return lines.map((line, idx) => {
      const cells = line.split('\t');
      if (cells.length < 12) {
        console.warn(`Line ${idx + 1} has fewer than 12 columns, skipping:`, line);
        return null;
      }
      const [
        action,
        firstName,
        middleName,
        lastName,
        suffix,
        jobTitle,
        country,
        countryOther,
        phoneType,
        phoneNumber,
        email,
        role
      ] = cells.map(c => c.trim());
      return {
        action,
        firstName,
        middleName,
        lastName,
        suffix,
        jobTitle,
        country,
        countryOther,
        phoneType,
        phoneNumber,
        email,
        role
      };
    }).filter(Boolean);
  }
  const reps = parseRaw(rawText);
  if (!reps.length) {
    console.warn("No valid AR rows found. Did you paste your Excel rows correctly?");
    return;
  }
  console.log(`Filling ${reps.length} person(s)…`);
  // ---------- Generic DOM helpers ----------
  function setInputValue(el, value) {
    if (!el || value == null || value === "") return;
    el.focus();
    el.value = value;
    el.dispatchEvent(new Event('input', { bubbles: true }));
    el.dispatchEvent(new Event('change', { bubbles: true }));
    el.blur();
  }
  function normalizeText(str) {
    return String(str || '')
      .toLowerCase()
      .replace(/[\s\-]+/g, '');
  }
  function setSelectByTextLoose(el, text) {
    if (!el || !text) return;
    const targetNorm = normalizeText(text);
    let found = false;
    for (const opt of el.options) {
      const optNorm = normalizeText(opt.text);
      if (optNorm === targetNorm) {
        opt.selected = true;
        el.value = opt.value;
        el.dispatchEvent(new Event('change', { bubbles: true }));
        el.dispatchEvent(new Event('input', { bubbles: true }));
        found = true;
        break;
      }
    }
    if (!found) {
      console.warn("Option not found in select for value:", text, el);
    }
  }
  function clickIfExists(el) {
    if (!el) return;
    el.click();
  }
  // Find the person block container based on First Name field
  function findPersonContainer(firstNameEl) {
    let el = firstNameEl;
    for (let i = 0; i < 15 && el; i++) {
      if (
        el.classList &&
        (el.classList.contains('container-control') ||
         (el.className && el.className.indexOf('container-control') !== -1))
      ) {
        return el;
      }
      el = el.parentElement;
    }
    return firstNameEl.parentElement;
  }
  // ---------- Wait/poll for Country (Other) inside this person's block ----------
  function waitAndFillCountryOther(container, idx, country, countryOther, maxTries = 20) {
    if (normalizeText(country) !== 'other') return;
    if (!countryOther) return;
    function tryOnce(triesLeft) {
      if (!container) return;
      const el =
        container.querySelector('input[id*="countryother"]') ||
        container.querySelector('input[name*="countryother"]') ||
        container.querySelector('input[placeholder*="Country (Other)"]');
      if (el) {
        setInputValue(el, countryOther);
        console.log(`Person ${idx}: Country (Other) filled`);
        return;
      }
      if (triesLeft <= 0) {
        console.warn(`Person ${idx}: Country (Other) field not found after waiting`);
        return;
      }
      setTimeout(() => tryOnce(triesLeft - 1), 200);
    }
    tryOnce(maxTries);
  }
  // ---------- Wait/poll for Phone Number inside this person's block ----------
  function waitAndFillPhone(container, idx, phoneNumber, country, maxTries = 20) {
    if (!phoneNumber) return;
    const isOther = normalizeText(country) === 'other';
    // small cleanup so "+91  98765  43210" becomes "+91 98765 43210"
    const cleanPhone = String(phoneNumber)
      .replace(/\s+/g, ' ')
      .trim();
    function tryOnce(triesLeft) {
      if (!container) return;
      let el;
      if (isOther) {
        // For Other – prefer inputs whose id/name includes BOTH phonenumber & other
        el =
          container.querySelector('input[id*="phonenumber"][id*="other"]') ||
          container.querySelector('input[name*="phonenumber"][name*="other"]');
      } else {
        // For US/Canada – prefer ones that do NOT have "other" in id/name
        el =
          container.querySelector('input[id*="phonenumber"]:not([id*="other"])') ||
          container.querySelector('input[name*="phonenumber"]:not([name*="other"])') ||
          container.querySelector('input[id*="phonenumber"]') ||
          container.querySelector('input[name*="phonenumber"]');
      }
      if (el) {
        setInputValue(el, cleanPhone);
        console.log(`Person ${idx}: Phone Number filled (country=${country})`);
        return;
      }
      if (triesLeft <= 0) {
        console.warn(`Person ${idx}: Phone Number field not found after waiting (country=${country})`);
        return;
      }
      setTimeout(() => tryOnce(triesLeft - 1), 200);
    }
    tryOnce(maxTries);
  }
  // ---------- Fill one person ----------
  function fillPerson(index, rep) {
    const idx = index; // 1-based
    const firstNameEl = document.getElementById(`activiti-firstname${idx}A`);
    if (!firstNameEl) {
      console.warn(`Person ${idx}: First Name field not found, stopping further filling.`);
      return false;
    }
    const container = findPersonContainer(firstNameEl);
    console.log(`Filling Person ${idx}`, rep);
    // 1) Action: Add / Delete
    const actionNorm = normalizeText(rep.action);
    const addRadio = document.getElementById(`activiti-action${idx}A_0`);
    const deleteRadio = document.getElementById(`activiti-action${idx}A_1`);
    if (actionNorm === 'delete' && deleteRadio) {
      clickIfExists(deleteRadio);
    } else if (addRadio) {
      clickIfExists(addRadio); // default Add
    }
    // 2) Names, job title, email
    setInputValue(firstNameEl, rep.firstName || '');
    setInputValue(document.getElementById(`activiti-middlename${idx}A`), rep.middleName || '');
    setInputValue(document.getElementById(`activiti-lastname${idx}A`), rep.lastName || '');
    setInputValue(document.getElementById(`activiti-jobtitle${idx}A`), rep.jobTitle || '');
    setInputValue(document.getElementById(`activiti-email${idx}A`), rep.email || '');
    // 3) Suffix dropdown (Jr / Sr)
    const suffixSel = document.getElementById(`activiti-suffix${idx}A`);
    if (suffixSel && rep.suffix) {
      setSelectByTextLoose(suffixSel, rep.suffix);
    }
    // 4) Role dropdown
    const roleSel = document.getElementById(`activiti-role${idx}A`);
    if (roleSel && rep.role) {
      setSelectByTextLoose(roleSel, rep.role);
    }
    // 5) Phone Type dropdown
    const phoneTypeSel = document.getElementById(`activiti-phonetype${idx}A`);
    if (phoneTypeSel && rep.phoneType) {
      setSelectByTextLoose(phoneTypeSel, rep.phoneType);
    }
    // 6) Country, Country (Other), Phone
    const countrySel = document.getElementById(`activiti-country${idx}A`);
    const country = rep.country || '';
    if (!countrySel) {
      console.warn(`Person ${idx}: Country select not found`);
    } else {
      if (country) {
        setSelectByTextLoose(countrySel, country);
      }
      // Wait for conditional fields
      waitAndFillCountryOther(container, idx, country, rep.countryOther);
      waitAndFillPhone(container, idx, rep.phoneNumber, country);
    }
    return true;
  }
  // ---------- Main ----------
  reps.forEach((rep, i) => {
    const idx = i + 1;
    fillPerson(idx, rep);
  });
  console.log("Done filling fields. Please review everything, then click SAVE and COMPLETE manually.");
}
// Helper with “remember last data” logic
function fillARFromPrompt() {
  const last = window._alfrescoARLastRaw || "";
  if (last) {
    const useLast = window.confirm(
      "Use the same AR data as last time?\n\nOK = reuse previous data\nCancel = paste new Excel rows"
    );
    if (useLast) {
      fillAR(last);
      return;
    }
  }
  const raw = window.prompt(
    "Paste your Excel AR rows (no header, one AR per line, copied directly from Excel):",
    ""
  );
  if (!raw) {
    console.log("No data pasted. Aborting.");
    return;
  }
  // cache for next time in this tab
  window._alfrescoARLastRaw = raw;
  fillAR(raw);
}
// Auto-run once when you execute the snippet:
fillARFromPrompt();

Answer 1

The provided VBA code is designed to implement soft-lock logic and manage task IDs within an Excel worksheet. It includes several key functions and event handlers:

1. IsInRange: Checks if a given range intersects with another range.
2. TopLeftCell: Returns the top-left cell of a specified range, accounting for merged cells.
3. IsProtectedEdit: Determines if an edit should be soft-locked based on the target range.
4. NormalizeCategory: Normalizes a category string by removing spaces and special characters.
5. CategoryNeedsID and IsBreakOrNoInv: Determine if a category requires an ID or falls under specific conditions.
6. Worksheet_SelectionChange: Captures the original value of a cell when the selection changes and tracks changes in specific cells.
7. Worksheet_Change: Handles changes in specific cells (I28 and I29) to manage task IDs and start timers based on category conditions.

The code effectively manages user interactions with the worksheet, ensuring that edits are controlled and that task IDs are handled according to the specified business logic. It also includes error handling to maintain application stability during operations.

If you need further assistance with specific parts of the code or additional functionalities, feel free to ask!