VBA help Required

// MOLF batch helper – paste once per session, then use bookmarklet or run molfBatchFill()
(function () {
  const LS_KEY_LAST_RAW = "molf_last_raw_batch";
  const LS_KEY_PROGRESS = "molf_batch_progress";
  function installHandler() {
    // global function so bookmarklet can call it
    window.molfBatchFill = function () {
      // ----- ESC emergency stop -----
      window._molfStop = false;
      window.addEventListener(
        "keydown",
        function (e) {
          if (e.key === "Escape") {
            window._molfStop = true;
            console.warn("[MOLF] ESC pressed. Will stop after current step.");
          }
        },
        { once: true }
      );
      function log(...a) {
        console.log("[MOLF]", ...a);
      }
      function warn(...a) {
        console.warn("[MOLF]", ...a);
      }
      // -------- get raw AR data (reuse or prompt) --------
      let raw = "";
      let storedRaw = "";
      try {
        storedRaw = localStorage.getItem(LS_KEY_LAST_RAW) || "";
      } catch (e) {
        warn("localStorage not available; resume/last-case features disabled.", e);
      }
      if (storedRaw) {
        const reuse = window.confirm(
          "MOLF: Use last saved AR data?\n\nOK = use last batch\nCancel = paste new rows"
        );
        if (reuse) {
          raw = storedRaw;
        }
      }
      if (!raw) {
        const hint =
          "Paste Excel rows (WITHOUT header).\n" +
          "Columns (TAB-separated):\n" +
          "FirstName | MiddleName | LastName | Role | JobTitle | Email | PhoneType | PhoneNumber | Country";
        const input = window.prompt(hint, storedRaw || "");
        if (!input) {
          alert("No AR data provided. Aborting.");
          return;
        }
        raw = input.trim();
        if (!raw) {
          alert("AR data is empty after trimming. Aborting.");
          return;
        }
        try {
          localStorage.setItem(LS_KEY_LAST_RAW, raw);
        } catch (e) {
          warn("Could not save last raw batch.", e);
        }
      }
      // -------- parse rows --------
      function parseRows(text) {
        const lines = text
          .split(/\r?\n/)
          .map((l) => l.trim())
          .filter((l) => l.length > 0);
        const out = [];
        lines.forEach((line, idx) => {
          const cells = line.split("\t").map((c) => c.trim());
          if (cells.length < 9) {
            warn(
              `Row ${idx + 1} has fewer than 9 columns (got ${cells.length}). Skipping.`,
              line
            );
            return;
          }
          const [
            firstName,
            middleName,
            lastName,
            role,
            jobTitle,
            email,
            phoneType,
            phoneNumber,
            country,
          ] = cells;
          out.push({
            index: idx + 1,
            firstName,
            middleName,
            lastName,
            role,
            jobTitle,
            email,
            phoneType,
            phoneNumber,
            country,
          });
        });
        return out;
      }
      const reps = parseRows(raw);
      if (!reps.length) {
        warn("No valid AR rows parsed from input.");
        return;
      }
      log(`Parsed ${reps.length} AR row(s).`);
      // -------- helpers --------
      function normalize(str) {
        return String(str || "")
          .toLowerCase()
          .replace(/\s+/g, "")
          .replace(/-/g, "");
      }
      function setInputValue(el, value, label) {
        if (!el) {
          warn(`Field element not found: ${label}`);
          return;
        }
        if (window._molfStop) return;
        el.focus();
        el.value = value ?? "";
        el.dispatchEvent(new Event("input", { bubbles: true }));
        el.dispatchEvent(new Event("change", { bubbles: true }));
        el.blur();
        log(`${label} filled:`, value);
      }
      function setSelectByTextLoose(el, text, label) {
        if (!el) {
          warn(`Select element not found: ${label}`);
          return;
        }
        if (!text) {
          warn(`${label}: value empty; leaving as is.`);
          return;
        }
        if (window._molfStop) return;
        const targetNorm = normalize(text);
        let found = false;
        for (const opt of el.options) {
          if (normalize(opt.text) === targetNorm) {
            el.value = opt.value;
            el.dispatchEvent(new Event("change", { bubbles: true }));
            el.dispatchEvent(new Event("input", { bubbles: true }));
            log(`${label} selected:`, opt.text);
            found = true;
            break;
          }
        }
        if (!found) {
          warn(`${label}: option not found for "${text}". Please select manually.`);
        }
      }
      // -------- identify WCIS ID for batch key --------
      const wcisEl =
        document.getElementById("WcisId") ||
        document.querySelector("input[name='WcisId']");
      const wcisId = wcisEl ? String(wcisEl.value || "").trim() : "";
      function simpleHash(str) {
        let h = 0,
          i,
          chr;
        if (!str) return "0";
        for (i = 0; i < str.length; i++) {
          chr = str.charCodeAt(i);
          h = ((h << 5) - h) + chr;
          h |= 0;
        }
        return String(h);
      }
      const batchKey = `wcis:${wcisId || "none"}|hash:${simpleHash(raw)}`;
      // -------- resume progress --------
      let startIndex = 0;
      try {
        const rawProg = localStorage.getItem(LS_KEY_PROGRESS);
        if (rawProg) {
          const prog = JSON.parse(rawProg);
          if (
            prog &&
            prog.batchKey === batchKey &&
            prog.nextIndex != null &&
            prog.nextIndex < reps.length
          ) {
            const resume = window.confirm(
              `MOLF: Detected unfinished batch for WCIS ${wcisId || "(unknown)"}.\n\n` +
                `Last completed AR index: ${prog.nextIndex} of ${reps.length}.\n` +
                `OK = resume from AR #${prog.nextIndex + 1}\nCancel = restart from AR #1`
            );
            startIndex = resume ? prog.nextIndex : 0;
          }
        }
      } catch (e) {
        warn("Could not read/parse progress from localStorage.", e);
      }
      if (startIndex >= reps.length) {
        log("All ARs in this batch appear processed already.");
        return;
      }
      const ar = reps[startIndex];
      log(`Processing AR #${startIndex + 1} of ${reps.length}:`, ar);
      // -------- mandatory-field check (MiddleName is optional) --------
      function isEmpty(v) {
        return v == null || String(v).trim() === "";
      }
      const missing = [];
      if (isEmpty(ar.firstName)) missing.push("FirstName");
      if (isEmpty(ar.lastName)) missing.push("LastName");
      if (isEmpty(ar.role)) missing.push("Role");
      if (isEmpty(ar.jobTitle)) missing.push("JobTitle");
      if (isEmpty(ar.email)) missing.push("Email");
      if (isEmpty(ar.phoneType)) missing.push("PhoneType");
      if (isEmpty(ar.phoneNumber)) missing.push("PhoneNumber");
      if (isEmpty(ar.country)) missing.push("Country");
      if (missing.length > 0) {
        const nameLabel = `${ar.firstName || "(no first)"} ${
          ar.lastName || "(no last)"
        }`.trim();
        warn(
          `Skipping AR #${startIndex + 1} (${nameLabel}) – missing: ${missing.join(
            ", "
          )}`
        );
        try {
          localStorage.setItem(
            LS_KEY_PROGRESS,
            JSON.stringify({ batchKey, nextIndex: startIndex + 1 })
          );
        } catch (e) {
          warn("Failed to save progress after skip.", e);
        }
        alert(
          `Skipped AR #${startIndex + 1} (${nameLabel}).\n\n` +
            `Missing fields: ${missing.join(", ")}.\n` +
            `Run MOLF again to continue with the next AR.`
        );
        return;
      }
      if (window._molfStop) {
        warn("Stopped before filling because ESC was pressed.");
        return;
      }
      // -------- map DOM fields --------
      const firstNameEl = document.getElementById("FirstName");
      const middleNameEl = document.getElementById("MiddleName");
      const lastNameEl = document.getElementById("LastName");
      const roleSel = document.getElementById("RoleType");
      const jobTitleEl = document.getElementById("JobTitle");
      const emailEl = document.getElementById("EmailAddress");
      const phoneTypeSel = document.getElementById("PhoneNumberType");
      const phoneEl =
        document.getElementById("PhoneNumber") ||
        document.querySelector('input[name="PhoneNumber"]');
      const countrySel =
        document.getElementById("Country") ||
        document.querySelector('select[name="Country"]');
      if (!firstNameEl || !lastNameEl) {
        warn(
          "FirstName or LastName fields not found. Are you on the Create Authorized Contact form?"
        );
        return;
      }
      // -------- fill fields --------
      setInputValue(firstNameEl, ar.firstName, "First Name");
      setInputValue(middleNameEl, ar.middleName, "Middle Name"); // optional
      setInputValue(lastNameEl, ar.lastName, "Last Name");
      setSelectByTextLoose(roleSel, ar.role, "Role");
      setInputValue(jobTitleEl, ar.jobTitle, "Job Title");
      setInputValue(emailEl, ar.email, "Email");
      setSelectByTextLoose(phoneTypeSel, ar.phoneType, "Phone# Type");
      setInputValue(phoneEl, ar.phoneNumber, "Phone#");
      setSelectByTextLoose(countrySel, ar.country, "Country");
      if (window._molfStop) {
        warn("Stopped after filling because ESC was pressed (no submit).");
        return;
      }
      // -------- save nextIndex BEFORE clicking submit --------
      const isLast = startIndex === reps.length - 1;
      try {
        localStorage.setItem(
          LS_KEY_PROGRESS,
          JSON.stringify({ batchKey, nextIndex: startIndex + 1 })
        );
      } catch (e) {
        warn("Failed to save progress before submit.", e);
      }
      // -------- click the right button --------
      const btnSubmit = document.querySelector('button[name="Create"]');
      const btnAddAnother = document.querySelector('button[name="SaveAddAnother"]');
      if (isLast) {
        log("This is the LAST AR in batch → clicking Submit.");
        if (btnSubmit) btnSubmit.click();
        else warn("Submit button (name=Create) not found. Click it manually.");
      } else {
        log("More ARs remaining → clicking Submit & Create Another.");
        if (btnAddAnother) btnAddAnother.click();
        else warn(
          "Submit & Create Another (name=SaveAddAnother) not found. Click it manually."
        );
      }
      log(
        `Done processing AR #${startIndex + 1} of ${reps.length}. ` +
          "After page reload, run molfBatchFill again (or click the bookmarklet)."
      );
    }; // end molfBatchFill
    console.log(
      "[MOLF] molfBatchFill() installed. Call molfBatchFill() or use the bookmarklet to run."
    );
  }
  installHandler();
  // auto-run once when you first paste it
  if (window.molfBatchFill) {
    window.molfBatchFill();
  }
})();